0

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

mc110
  • 2,825
  • 5
  • 20
  • 21
Frank Liu
  • 553
  • 1
  • 5
  • 6
  • 1
    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? – dnoeth Jul 02 '14 at 07:59
  • 1
    problem solved. it's two single quotes is correct here. 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 .... '; set str_sql = str_sql || 'set ...'; – Frank Liu Jul 03 '14 at 01:50

1 Answers1

0

The problem might be on quotation. Instead of this

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

Use double quotation.

SET str_sql = 'UPDATE TABLE
                SET COLA = 0,
                    COLB = 'test'';
EXECUTE IMMEDIATE str_sql;
Charlesliam
  • 1,293
  • 3
  • 20
  • 36
  • I'm not familiar with this programming language, but I would have guessed that double quoting the str_sql variable or escaping the single quotes would have been the way to go. – Dan Bracuk Jul 02 '14 at 02:40
  • Have tried reversing the quotes? Double quotes is outside and single quote within the word `test`. – Charlesliam Jul 02 '14 at 03:15
  • tried, but looks teradata only support single quote for string. – Frank Liu Jul 02 '14 at 06:06