All Forums Database
mike_nguyen 5 posts Joined 03/06
31 Mar 2006
3706 Syntax error with select statement

Hi,I'm a newbie in teradata, I'm trying to use the following select statement that works in Oracle db, but it doesn't work in teradata. Would some one help me with the syntax.select * from table_a where col1='A' and col2 <= current_timestamp - (select col_A from table_B where col_B='test');thanks,michael

dnoeth 4628 posts Joined 11/04
02 Apr 2006

Hi Michael, try"and col2 <= (select current_timestamp - col_A from table_B where col_B='test')"Depending on the datatype of col2 you'll have to do a CAST...Dieter

Dieter

mike_nguyen 5 posts Joined 03/06
03 Apr 2006

Hi Dieter,Thanks for response , the datatype for col2 is timestamp and the col_A is an integer.I used your suggestion, and I'm getting a 5407 error code: Invalid operation on an ANSI Datetime or Interval value.How would you do a CAST? Basically, I want to get the current_timestamp minus the some number days.Michael

mike_nguyen 5 posts Joined 03/06
03 Apr 2006

I figured it out.select (cast (current_timestamp) as date - col_a from ....Thanks,Michael

winnieray 1 post Joined 09/12
24 Sep 2012

CAST ( SUBSTRING( CAST(msg_sts.prcss_ts AS CHAR (19))  , 0, 11) AS DATE )  > CAST ( SUBSTRING( CAST(tmp2.line_cre_ts AS CHAR (19))  , 0, 11) AS DATE) 
 
i am getting the same 3706 syntax error for the above cast statement

Qaisar Kiani 337 posts Joined 11/05
24 Sep 2012

The Substring syntax is not correct.
You either use SUBSTR(text, 0, 11) OR
SUBSTRING(text FROM 0 FOR 11)
 

PrasannaDevi 1 post Joined 12/12
03 Dec 2012

Hi ,
I'm a newbie to Teradatae , tyring to fine tune this piece of code, but getting the 3706 error in Teradata.
Here is the code,please help...

SEL APPS_ABBOTT_CUSTOMER_ID, IC_ABBOTT_CUSTOMER_ID FROM (

(SEL DISTINCT  IC.RULE_PACKAGE_VERSION_ID, ABBOTT_CUSTOMER_ID AS IC_ABBOTT_CUSTOMER_ID , IMS_NUMBER

FROM IC_UNIVERSE_ALIGNED IC

INNER JOIN  BRR_BATCH_RULE_PACKAGE P on  IC.RULE_PACKAGE_VERSION_ID = P.RULE_PACKAGE_VERSION_ID

                                                               AND P.BRR_BATCH_ID = CAST ('$$BRR_BATCH_ID' AS DECIMAL)

WHERE IC.DDS_ACTIVE_FLAG = 'Y'

--AND IC.DATA_MONTH = CAST('$$DATA_MONTH'  AS DATE FORMAT 'MM/DD/YYYY')

AND IC.INC_COMPN_ELIGIBILITY_FLAG = 'Y'

AND IC.IMS_NUMBER IS NOT NULL

AND IC.IMS_NUMBER <> '') AS X,

(SEL DISTINCT  C.RULE_PACKAGE_VERSION_ID, ABBOTT_CUSTOMER_ID AS APPS_ABBOTT_CUSTOMER_ID, IMS_NUMBER

FROM DDSP.APPS_UNIVERSE_THIN A

INNER JOIN IC_UNIVERSE_RULE C on A.VERSION_ID = C.DDS_APPS_VERSION_ID

                                                                and C.DDS_ACTIVE_FLAG = 'Y'

INNER JOIN  BRR_BATCH_RULE_PACKAGE P on  C.RULE_PACKAGE_VERSION_ID = P.RULE_PACKAGE_VERSION_ID

                                                               AND P.BRR_BATCH_ID = CAST ('$$BRR_BATCH_ID' AS DECIMAL)

WHERE A.DDS_ACTIVE_FLAG = 'Y'

AND IC.IMS_NUMBER IS NOT NULL

AND IC.IMS_NUMBER <> '') AS Y

WHERE  X.RULE_PACKAGE_VERSION_ID =  X.RULE_PACKAGE_VERSION_ID

AND X.IMS_NUMBER = Y.IMS_NUMBER

AND X.ABBOTT_CUSTOMER_ID <> Y.ABBOTT_CUSTOMER_ID)

UNION

(SEL DISTINCT  IC.RULE_PACKAGE_VERSION_ID, ABBOTT_CUSTOMER_ID AS IC_ABBOTT_CUSTOMER_ID, MAX_ID

FROM IC_UNIVERSE_ALIGNED IC

INNER JOIN  BRR_BATCH_RULE_PACKAGE P on  IC.RULE_PACKAGE_VERSION_ID = P.RULE_PACKAGE_VERSION_ID

                                                               AND P.BRR_BATCH_ID = CAST ('$$BRR_BATCH_ID' AS DECIMAL)

WHERE IC.DDS_ACTIVE_FLAG = 'Y'

--AND IC.DATA_MONTH = CAST('$$DATA_MONTH'  AS DATE FORMAT 'MM/DD/YYYY')

AND IC.INC_COMPN_ELIGIBILITY_FLAG = 'Y'

AND (IC.IMS_NUMBER IS NULL OR  IC.IMS_NUMBER =  '')) AS X,

(SEL DISTINCT  C.RULE_PACKAGE_VERSION_ID, ABBOTT_CUSTOMER_ID AS APPS_ABBOTT_CUSTOMER_ID, MAX_ID

FROM DDSP.APPS_UNIVERSE_THIN A

INNER JOIN IC_UNIVERSE_RULE C on A.VERSION_ID = C.DDS_APPS_VERSION_ID

                                                                and C.DDS_ACTIVE_FLAG = 'Y'

INNER JOIN  BRR_BATCH_RULE_PACKAGE P on  C.RULE_PACKAGE_VERSION_ID = P.RULE_PACKAGE_VERSION_ID

                                                               AND P.BRR_BATCH_ID = CAST ('$$BRR_BATCH_ID' AS DECIMAL)

WHERE A.DDS_ACTIVE_FLAG = 'Y'

AND (IC.IMS_NUMBER IS NULL OR  IC.IMS_NUMBER =  '')) AS Y

WHERE  X.RULE_PACKAGE_VERSION_ID =  X.RULE_PACKAGE_VERSION_ID

AND X.MAX_ID = Y.MAX_ID
AND X.ABBOTT_CUSTOMER_ID <> Y.ABBOTT_CUSTOMER_ID));
You must sign in to leave a comment.