All Forums Analytics
bdwebman 5 posts Joined 03/11
31 Mar 2011
SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

I am troubling over this error as I am not sure what the cause might be. Any help would be greatly appreciated.

select Id, (select
case
when Fall_score = b.MaxScore then 'Fall'
when Spring_score = b.MaxScore then 'Spring'
from sandbox.testtable t where t.Id = b.Id
) as MaxScoreCategory,MaxScore
from (select Id, max(Score) as MaxScore
from (select Id, 'Fall', Fall_score from sandbox.testtable union all
select Id, 'Spring', Spring_score from sandbox.testtable)
a (Id, Category, Score)
group by Id

) b

Result:
SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword.

Jim Chapman 449 posts Joined 09/04
31 Mar 2011

Which Teradata version are you using? Scalar subqueries in the select list were not supported until 13.0.

bdwebman 5 posts Joined 03/11
31 Mar 2011

The DB version is 12 (SQL asst is 13). can you think of a way to rewrite this?

bdwebman 5 posts Joined 03/11
31 Mar 2011

IF possible it will anyone could rewrite with LEFT JOIN include the N/A and end which I forgot -- I know Dieter has some examples. Any help is greatly appeciated-- again - this will NOT work in TD 12. 3706
select
id,
(select
case
when Category_1 = b.MaxScore then 'Category_1'
when Category_2 = b.MaxScore then 'Category_2'
when Category_3 = b.MaxScore then 'Category_3'
else 'NA'
end
from YourTable t where t.id = b.id
) as MaxScoreCategory,
MaxScore
from (
select
id,
max(Score) as MaxScore
from (
select id, 'Category_1', Category_1 from YourTable union all
select id, 'Category_2', Category_2 from YourTable union all
select id, 'Category_3', Category_3 from YourTable
) a(id, Category, Score)
group by id
) b

dnoeth 4628 posts Joined 11/04
01 Apr 2011

Looks like you want the max of three columns.
Your query is overly complex, you don't need any Scalar Subquery or Left Join to get the result set:

SELECT id,
CASE
WHEN category_1 > category_2 AND category_1 > category_3 THEN category_1
WHEN category_2 > category_3 THEN category_2
WHEN category_3 IS NOT NULL THEN category_3
ELSE 'NA'
END AS MaxScore,
CASE
WHEN category_1 > category_2 AND category_1 > category_3 THEN 'category_1'
WHEN category_2 > category_3 THEN 'category_2'
WHEN category_3 IS NOT NULL THEN 'category_3'
ELSE 'NA'
END AS MaxScoreCategory
FROM yourtable

Dieter

Dieter

bdwebman 5 posts Joined 03/11
04 Apr 2011

Dieter - perfect. this works. I made the example a little too simple. Would you use the same strategy for 10 categories? This query totally works! Thanks! If you would use something different, than what would that be

dnoeth 4628 posts Joined 11/04
06 Apr 2011

A huge CASE just looks ugly, but it's probably the most efficient way, so i'd stick with it for much more than 10 categories.
I once wrote a batch which created a nested case with a few thousand values based on the data in a column. This resulted in more than 200kb souce code :-)

Dieter

Dieter

jainayush007 50 posts Joined 03/11
08 Apr 2011

Just another way to do it could be:-

sel a.* from
(select Id, 'Fall' as category, Fall_score score from sandbox.testtable union all
select Id, 'Spring' , Spring_score from sandbox.testtable)A
QUALIFY ROW_NUMBER () OVER (PARTITION BY a.id ORDER BY a.score DESC) = 1;

Since this may mean, 1 additional roll of the the same table for each category, it may not be as efficient as dieter suggested.

Correct Dieter ;)

dirty 1 post Joined 03/10
22 Feb 2012

Hello i have the same problem but in update the inner selet goes its self but update dont:

 

UPDATE tsp_project_0002

FROM

(SELECT 

x.analisys_period  AS analisys_period

, CAST (CASE WHEN LEFT(TRIM(x.analisys_period) , 6) = 'YTD' THEN z.cy_first_month WHEN LEFT( TRIM(x.analisys_period) , 6)  = 'MTD' THEN  z.current_month ELSE CAST(LEFT( TRIM(x.analisys_period) , 6)  AS DECIMAL (6,0)) END AS DECIMAL (6,0)) period_from

 

FROM

 intccr02_support.tsp_project_0002 x

 

CROSS JOIN tsp_project_dates z

)v

 

SET analisys_from = v.period_from

WHERE v.analisys_period = tsp_project_0002.analisys_period

 

Can you help me ?

vasu6 12 posts Joined 10/12
23 Apr 2013

HI Dieter,
I have some questions on DBC space ?
First of all i need to increatse space on DBC .(Here we can't add more disk sapce )only the option is we can reduce space from other db's or users ,then the space is  should be increase on DBC ? Right ?
And we use some query  i.e ,
SELECT tablename, SUM(currentperm), SUM(peakperm) FROM dbc.tablesize WHERE databasename = 'dbc' GROUP BY 1 QUALIFY RANK() OVER (ORDER BY SUM(currentperm) DESC) <= 20;
Result is : 
 

TransientJournal 210,030,166,016.00 210,030,166,016.00;EventLog 6,782,329,856.00 6,782,329,856.00;DBQLogTbl 3,166,243,840.00 3,166,243,840.00;DBQLSqlTbl 1,779,373,056.00 1,779,373,056.00;SW_Event_Log 1,551,524,864.00 1,551,524,864.00

;RCEvent 40,189,952.00 40,189,952.00;TVFields 39,965,184.00 45,026,816.00;

TVM 25,444,352.00 27,667,456.00;AccessRights 9,964,032.00 10,184,192.00

;DataBaseSpace 7,659,520.00 7,669,248.00;TextTbl 5,172,224.00 8,936,448.00

;Indexes 1,152,000.00 1,177,600.00;ResUsageSps 1,106,944.00 1,106,944.00

;ResUsageSvpr 864,256.00 864,256.00;ErrorMsgs 797,696.00 797,696.00

;ResUsageIvpr 403,456.00 403,456.00;ConstantDefs 333,824.00 333,824.00

;ConstantValues 294,912.00 294,912.00;PasswordRestrictions 163,840.00 163,840.00

ResUsageSawt 120,832.00 120,832.00

 

Like this i got result ..First question is  this query is Right or not ? To find this Result ? ?

 

in this what are all the delete from dictonary tables.Once we delete some space  from above result, and we get some space add in to DBC Right ? ?

 

Can you pelase help me inthis query ?

How can i delete space  to get more increase in dbc ? ?

Please help me ? any one 

Thanks in advance

 

Thanks,
Vasudeva Rao

dnoeth 4628 posts Joined 11/04
23 Apr 2013

Hi Vasudeva,
please post new questions as a new topic.
 
You're correct, the perm of dbc can only be increased by decreasing the size of other databases or adding new disk space (=new node).
 
Only a few dbc tables can actually be modified, in your case the ResUsage*, DBQL*, EventLog, SW_Event_log.
Deleting old data from those tables (and copy it to some history database) is good practice and there should be alreday a job for this.
 
In your case the TransientJournal is by far the biggest table indicating there is a huge transaction running, you should investigate which job was causing this.
 
What is the max perm space of dbc and how much is currerntly in use.

SELECT 
  -- assigned perm space in GB
  SUM(MaxPerm) / 1024**3 (DEC(10,2)) AS MaxPermGB, 

  -- currently used perm space
  SUM(CurrentPerm) / 1024**3 (DEC(10,2)) AS CurrentPermGB, 

  -- currently used perm space based on skew
  MAX(CurrentPerm) * (HASHAMP()+1) / 1024**3 (DEC(10,2)) AS SkewedPermGB
FROM dbc.DiskSpaceV
WHERE DatabaseName = 'dbc'

 
Dieter

Dieter

vasu6 12 posts Joined 10/12
21 Jun 2013

Hi Dieter,
Sorry for delay response..we ran that query..the result is MAXpermGB =155.84 and currentpermGB=31.77 and SkewedPermGB= 31.86..this is the result...
As per your told can i delete (ResUsage*, DBQL*, EventLog, SW_Event_log).... old data ? ?
If possible can you post command for this dropping data ?
Thanks in advance...

Thanks,
Vasudeva Rao

vasu6 12 posts Joined 10/12
21 Jun 2013

HI Dieter,
I need to configure TD Viewpoint 13.00 or 13.10 on windows and linux..Can you please help me on this ?
Or else pls share how to configure and install TD Viewpoint on linnux or windows to me ?
My id : vvdrmca@gmail.com....send your mail id pls ?
And where i have to download viewpoint s/w and how to install and configure ? ?
Is there any other dependency s/w befoure instaling Viewpoint ? 

Thanks,
Vasudeva Rao

dnoeth 4628 posts Joined 11/04
23 Jun 2013

Hi Vasudeva,
are you the DBA? You should read 
CHAPTER 12 Recommended Housekeeping Tasks

Cleaning Out Frequently Updated Logs

before deleting system logs.

 

Deleting is just a simple DELETE.

 

 

Regarding Viewpoint:

Viewpoint is an appliance on a TMS (Teradata managed server), you can download the latest release at T@YS.

But 13.10 is an outdated version you should get the latest release 14.10.

 

There are manuals for configuring Viewpoint available at www.info.teradata.com

 

But: please post new questions as a new topic.

 

Dieter

Dieter

sasha 4 posts Joined 06/14
15 Jun 2014

Hi Dieter,
Can you help me with the following function. I am getting this error
 3706: Syntax error: expected something between 'return' keyword and the 'select' keyword
code:
Replace FUNCTION abcdb.udf_getPromoIdByPromoCode

(

p_PromoCode varchar(20),

p_ServiceId int,

p_PrivateId int

 

)

RETURNS int

LANGUAGE SQL

CONTAINS SQL

DETERMINISTIC

-- BEGIN

COLLATION INVOKER

INLINE TYPE 1

--AS

--BEGIN

--DECLARE v_PromoId int;

 

return SELECT 

p.Id INTO v_PromoId 

FROM 

mcdb.Promotions p

INNER JOIN abcdb.AppServiceMasterOptionsPromotions asmop  ON p.Id = asmop.PromotionId 

INNER JOIN abcdb.AppServiceMasterOptions options ON options.id = asmop.AppServiceMasterOptionId 

WHERE 

options.serviceID = p_ServiceId  

AND options.PrivateID = p_PrivateId 

AND Code = p_PromoCode 

AND IsActive = 1

 

--RETURN v_PromoId

END;

 

Thanks,

Sasha

dnoeth 4628 posts Joined 11/04
15 Jun 2014

Hi Sasha,
you can't do a SQL function like this in Teradata.
SQL UDFs are limited to simple calculations like those you can do in the SELECT list, but no kind of  Scalar Subquery or similar.
There's no workaround, you just might put the base SELECT in a view and join to it.

Dieter

sasha 4 posts Joined 06/14
15 Jun 2014

Thanks Dieter! Will create views then.
Sasha

mohammh 2 posts Joined 06/14
19 Jun 2014

Getting the below error for the SQL listed below.

 

Teradata ODBC Error

SQLCODE: -3707

SQLSTATE: 42000

MESSAGE: [NCR][ODBC Teradata][Teradata Database] Syntax error, expected something like an 'IN' keyword between the word 'fill_type_cd' and '|'.Unable to get catalog string.

 

SELECT pfpr.fill_enter_dt
 ,pfpr.str_nbr
 ,CASE WHEN mdlcat.reject_cat_cd IS NULL THEN 16 ELSE mdlcat.reject_cat_cd END AS reject_cat_cd
 ,CASE WHEN mdlcat.reject_cat_decode IS NULL THEN CAST('Other' AS VARCHAR(50)) ELSE mdlcat.reject_cat_decode END AS reject_cat_decode
 ,COUNT(DISTINCT(pfpr.str_nbr||pfpr.rx_nbr)) AS tpr_ct

FROM prdedwvwh.prescription_fill_plan_reject pfpr
LEFT JOIN prdresultsdss.labormodel_tpr_groupings mdlcd
  ON pfpr.dl_reject_cd = mdlcd.dl_reject_cd
LEFT JOIN prdresultsdss.labormodel_tpr_reject_catg mdlcat
  ON mdlcd.rej_catg_cd = mdlcat.reject_cat_cd

WHERE pfpr.fill_enter_dt BETWEEN '2014-02-01' AND '2014-02-28'
  AND pfpr.reject_nbr = 1

GROUP BY pfpr.fill_enter_dt
   ,pfpr.str_nbr
   ,CASE WHEN mdlcat.reject_cat_cd IS NULL THEN 16 ELSE mdlcat.reject_cat_cd END
   ,CASE WHEN mdlcat.reject_cat_decode IS NULL THEN CAST('Other' AS VARCHAR(50)) ELSE mdlcat.reject_cat_decode END

ORDER BY pfpr.fill_enter_dt
   ,pfpr.str_nbr
   ,CASE WHEN mdlcat.reject_cat_cd IS NULL THEN 16 ELSE mdlcat.reject_cat_cd END

 

 

dnoeth 4628 posts Joined 11/04
20 Jun 2014

Are you shure it's this SQL?
There's no fill_type_cd within it and it should run as-is.

Dieter

rajat051984 2 posts Joined 03/15
11 Mar 2015

Getting  Error - Teradata execute: Syntax error: expected something between '(' and the 'select' keyword.
for below query submitted through SAS. Can you kindly modify the below query ?

rsubmit;
proc sql;
connect to Teradata (user = 'xxxxx' pass='xxxxx' server='edw' database=udw mode=teradata CONNECTION=GLOBAL);

execute(
create multiset volatile table merch_state as
 (
select rec.location_id,rec.zip_code ,rec.store_state_nm from
       (
         select RANK()OVER (PARTITION by g.location_id  ORDER BY  update_dttm DESC ) as rank1,
        g.location_id, g.update_dttm ,g.store_zip_cd as zip_code ,g.store_state_nm
        from sv_activity_tracking g sample 50
       )rec
     where rec.rank1 = 1


 ) with data  on commit preserve rows
 ) by teradata;

 quit; 

 

rajat051984 2 posts Joined 03/15
11 Mar 2015

I think below is the substitute query for the same. Can somebody kindly confirm ?

rsubmit;
proc sql;
connect to Teradata (user='xxxxx' pass='xxxxx' server='edw' database=udw mode=teradata CONNECTION=GLOBAL);

execute(
create multiset volatile table merch_state as
 (
  select a.* from
 (select location_id, update_dttm, store_zip_cd as zip_code, store_state_nm
  from sv_activity_tracking /*sample 60*/ )a
  QUALIFY ROW_NUMBER()OVER (PARTITION by a.location_id  ORDER BY  a.update_dttm DESC) = 1

 ) with data  on commit preserve rows
 ) by teradata;
quit;

 

You must sign in to leave a comment.