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.
Error with Teradata connector
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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\' ) ;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Error with Teradata connector
This proble was resolved.
In teradata connector stage properties,change mode from ANSI to Teradata then it works.
Thanks,
In teradata connector stage properties,change mode from ANSI to Teradata then it works.
Thanks,
eswar