Stored Procedure statement error

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

Stored Procedure statement error

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;

#DaveWellman, #Fred , #info-eng-team-t ,#


Accepted Solutions
Junior Contributor

Re: Stored Procedure statement error

You're using ODBC-syntax for SUBSTRING which is sometimes automatically translated by the ODBC-driver, but never within an SP.

 

Valid in Teradata is either 

 (substring (tb_name FROM 1 FOR 13)

or

 (substr (tb_name ,1 , 13)

 

1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: Stored Procedure statement error

You're using ODBC-syntax for SUBSTRING which is sometimes automatically translated by the ODBC-driver, but never within an SP.

 

Valid in Teradata is either 

 (substring (tb_name FROM 1 FOR 13)

or

 (substr (tb_name ,1 , 13)

 

Senior Apprentice

Re: Stored Procedure statement error

Hi,

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

You've got:

substring ( tb_name , 1 , 16)

Normally this would be:

substring ( tb_name  from  1 for 16)

HTH
Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Stored Procedure statement error

Thank you dnoeth. It worked . 

I appreciate your quick turn . :-)

Teradata Employee

Re: Stored Procedure statement error

Thank you much Dave.

yes I agree with you. It worked now :-) 

I changed it from substring to substr.