execute immediate with sting in sql statement in teradata

Database
Enthusiast

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

Tags (1)
7 REPLIES
Senior Apprentice

Re: execute immediate with sting in sql statement in teradata

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?

Enthusiast

Re: execute immediate with sting in sql statement in teradata

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

Senior Apprentice

Re: execute immediate with sting in sql statement in teradata

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''';
Enthusiast

Re: execute immediate with sting in sql statement in teradata

Hi, Dieter

Really thanks for your help.

:)

Best Regards,

Frank Liu

Re: execute immediate with sting in sql statement in teradata

any terdata dumps for v14

Teradata Employee

Re: execute immediate with sting in sql statement in teradata

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.

Teradata Employee

Re: execute immediate with sting in sql statement in teradata

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.