execute immediate with sting in sql statement in teradata

[luckylh]

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]

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?

[luckylh]

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]

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

[luckylh]

Hi, Dieter

Really thanks for your help.

:)

Best Regards,

Frank Liu

[sriram_rengan]

any terdata dumps for v14

[hasanshan]

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]

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.