Error with Teradata connector
Posted: Tue Jan 21, 2014 1:54 am
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.
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.