3706 Syntax error with select statement

Database
Enthusiast

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
6 REPLIES
Senior Apprentice

Re: 3706 Syntax error with select statement

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
Enthusiast

Re: 3706 Syntax error with select statement

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
Enthusiast

Re: 3706 Syntax error with select statement

I figured it out.

select (cast (current_timestamp) as date - col_a from ....

Thanks,

Michael

Re: 3706 Syntax error with select statement

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

Enthusiast

Re: 3706 Syntax error with select statement

The Substring syntax is not correct.

You either use SUBSTR(text, 0, 11) OR

SUBSTRING(text FROM 0 FOR 11)

Re: 3706 Syntax error with select statement

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));