All Forums Database
luckylh 5 posts Joined 04/14
01 Jul 2014
execute immediate with sting in sql statement in teradata

I've met the problem when using execute immediate in Teradata.

SET str_sql = 'UPDATE TABLE
SET COLA = 0';
EXECUTE IMMEDIATE str_sql;

The above code works fine.

SET str_sql = 'UPDATE TABLE
SET COLA = 0,
COLB = ''test''';
EXECUTE IMMEDIATE str_sql;

The above code with string returns error.
The following is the error message:

Executed as Single statement. Failed [3706 : 42000] Table:Syntax error: expected something between a string or a Unicode character literal and the word 'test'.
Elapsed time = 00:00:00.212

STATEMENT 1: CALL failed.
Anyone know how to invoke the execute immediate with String in the sql? Thanks!
Frank Liu

dnoeth 4628 posts Joined 11/04
02 Jul 2014

Hi Frank,
your code is correct, a single quote within a string must be replaced by two single quotes. So this should run as-is.
Can you show your exact query?
Or the full SP source?

Dieter

luckylh 5 posts Joined 04/14
02 Jul 2014

Hi, Dieter
Thanks for your reply.
Problem solved. The two single quotes is correct. The problem here is because of the enter in the sql statement. it should be written either in one line or use || to connect strings in different lines, like
set str_sql = 'update table ';
set str_sql = str_sql || 'set cola = 0, '; 
set str_sql = str_sql || 'set colb = ''test''';
execute immediate str_sql;
Frank Liu

dnoeth 4628 posts Joined 11/04
02 Jul 2014

Hi Frank,
this is strange as it should work with linebreaks, too. In fact I prefer it over concatenating as the SQL code is more readable and you don't have to check for a leading or trailing blank in your strings.
I just put your code in an SP and got a "table doesn't exist" error. 
Btw, you can simplify your code to:

set str_sql = 'update table '
    || 'set cola = 0, '
    || 'set colb = ''test''';

 

 

Dieter

luckylh 5 posts Joined 04/14
03 Jul 2014

Hi, Dieter
Really thanks for your help.
:)
Best Regards,
Frank Liu

sriram.rengan 1 post Joined 07/14
03 Jul 2014

any terdata dumps for v14

hasanshan 2 posts Joined 02/14
13 May 2016

I getting following error then running my stored procedure having dynamic SQL.
CALL Failed 7689: PROC_POPULATE_TEST:Invalid dynamic SQL statement.
This is my stored procedure.

REPLACE PROCEDURE DT_SDMT.PROC_POPULATE_TEST( )

BEGIN

 

DECLARE UPD_STRING VARCHAR(6000);

 

--------------------------------------------------------------- COMMON----------------------------------------------------------------------------------------

SET UPD_STRING = 'select * from DD_SDMT.AccountCode_Dim;';

                           

 

Execute Immediate UPD_STRING;

 

END;

 

 

CALL DT_SDMT.PROC_POPULATE_TEST();

 

Anyone know how to fix this issue.

 

Thanks.

Fred 1096 posts Joined 08/04
14 May 2016

Answered in the other thread. Please open a new topic for new questions - go to hte main page for this forum and click the big button at the top.

You must sign in to leave a comment.