Below statement in the procedure get created successfully during compilation. But when I call the procedure , it gives me syntax error.
The same is running absolutely fine in teradata sql assistant. I have checked all the possibilities but I don't get the result. Please help me.
Syntax error: expected something between the word 'tb_name' and ','.
SyntaxEditor Code Snippet
SET _StatementText = 'INSERT INTO fdws46_int_me_work.TABLE_INFO select ''FDWS46_INT_ME_META'' as db_name , "TableName" as tb_name from dbc.tablesV where (substring (tb_name ,1 , 13) like ''me@_reference@_%'' ESCAPE ''@'' and regexp_similar ( trim ( strtok ( tb_name , ''_'' , 3)) , ''(^[0-9]*$)'') = 1 ) or ( substring ( tb_name , 1 , 16) like ''me@_sm@_reference@_%'' ESCAPE ''@'' and regexp_similar ( trim ( strtok ( tb_name , ''_'' , 4)) , ''(^[0-9]*$)'') = 1 ) and databasename =''FDWS46_INT_ME_META'' and trim ( strtok( tb_name , ''_'' , character_length ( ( otranslate ( tb_name , otranslate ( tb_name , ''_'', ''''),'''') ) )+1) ) > '''||_StartTimeStamp||''';'; set _ResultSet = _StatementText; CALL DBC.SysExecSQL(_StatementText); FOR vFor AS cName CURSOR FOR select db_name, tb_name from fdws46_int_me_work.TABLE_INFO DO SET _StatementText = 'DROP TABLE '||vFor.db_name||'.'||vFor.tb_name||';'; CALL DBC.SysExecSQL(_StatementText); END FOR;
Solved! Go to Solution.
Not sure if this is the cause, but your SUBSTRING code doesn't look correct. It looks like you're mixing SUBSTRING with the alternative SUBSTR syntax. (I'm not sure how this code works in SQLA - ODBC translation?)
substring ( tb_name , 1 , 16)
Normally this would be:
substring ( tb_name from 1 for 16)