Error with Teradata connector

[eswar1]

Hi,

I am stuck with Teradata SQL query which is working in database but in datastage it is not working.
My job is one RowGen connected to Target Teradata Connector stage.
In TCon stage write mode is user defined SQL and SQL is

DELETE FROM TEST_MOSAIC_DB.FLOWN_TICKET WHERE TICKET_RPT_DT <
( SELECT CAST(TRIM(CONCAT((EXTRACT(YEAR FROM LST_PRCSSED_DT)- (SELECT CAST(VAL AS BYTEINT) FROM test_LOG_DB.ctl_nm_val
WHERE NM='DataRetentionPeriod')),'1201')) AS DATE FORMAT 'YYYYMMDD') from test_LOG_DB.MIR_PRCS_DT_CTL_TBL WHERE prcs_nm='MONTHLY' ) ;

For Every run it is generating error in Director as

Tcon_Purge,0: [IIS-CONN-TERA-005004] RDBMS code 3706: Syntax error: expected something between '(' and '('. SQL statement: DELETE FROM TEST_MOSAIC_DB.FLOWN_TICKET WHERE TICKET_RPT_DT < ( SELECT CAST(TRIM(CONCAT((EXTRACT(YEAR FROM LST_PRCSSED_DT)- (SELECT CAST(VAL AS BYTEINT) FROM test_LOG_DB.ctl_nm_val WHERE NM='DataRetentionPeriod')),'1201')) AS DATE FORMAT 'YYYYMMDD') from test_LOG_DB.MIR_PRCS_DT_CTL_TBL WHERE prcs_nm='MONTHLY' ) (CC_TeraDBStatement::dbsBindInputs, file CC_TeraDBStatement.cpp, line 621).

Please help me to sort out this issue.

[ray.wurlod]

Try "escaping" the quote characters.

Code: Select all

DELETE FROM TEST_MOSAIC_DB.FLOWN_TICKET WHERE TICKET_RPT_DT <
( SELECT CAST(TRIM(CONCAT((EXTRACT(YEAR FROM LST_PRCSSED_DT)- (SELECT CAST(VAL AS BYTEINT) FROM test_LOG_DB.ctl_nm_val
WHERE NM=\'DataRetentionPeriod\')),\'1201\')) AS DATE FORMAT \'YYYYMMDD\') from test_LOG_DB.MIR_PRCS_DT_CTL_TBL WHERE prcs_nm=\'MONTHLY\' ) ; 

[eswar1]

This proble was resolved.
In teradata connector stage properties,change mode from ANSI to Teradata then it works.

Thanks,