Why does EXECUTE IMMEDIATE in a trigger throw "Correlation 'new_name' not found"?

[Breck_Carter]

From: Irene Yu

Newsgroups: sybase.public.sqlanywhere.general

Subject: Correlation error in trigger

Date: 6 Nov 2010 01:17:46 -0800

Good day,

Have a script being used in triggers in 3 tables and it works fine except for one table wheren it's giving an error "Correlation 'new_name' not found"

Script:

REFERENCING OLD AS old_name NEW AS new_name
FOR EACH ROW

Set @ls_sql_old = 'set @ls_old_char_value =
convert(char(200), old_name.'+@ls_column_name + ');';
Set @ls_sql_new = 'set @ls_new_char_value =
convert(char(200), new_name.'+@ls_column_name +');' ;

Execute immediate @ls_sql_new;
Execute immediate @ls_sql_old;

The @LS_column_name holds the column name which is fetched from another table in a cursor.

Error occurs in the execute immediate script.

I have tried adding specific statements just right before the execute immediate script which will have the same statement at one point in both @LS_sql_new & @LS_sql_old statements, like:

set @ls_old_char_value = convert(char(200),
old_name.lastname);
set @ls_new_char_value = convert(char(200),
new_name.lastname);

If cursor fetches @LS_column_name = 'lastname', it will have no problem running the execute immediate scripts. However on the next loop wherein @LS_column_name holds another column name, it will raise the error "Correlation 'new_name' not found"

Using ASA 9.0.2.3044

Would greatly appreciate any insight.

Thanks.

[Breck_Carter]

This looks like a deficiency in version 9.0.2. The problem remains in 9.0.2.3575 and is gone in 10.0.1.3415.

Here is a reproducible test case:

CREATE TABLE t (
   key_1          INTEGER NOT NULL PRIMARY KEY,
   non_key_1      VARCHAR ( 100 ) NOT NULL,
   non_key_2      VARCHAR ( 100 ) NOT NULL,
   non_key_3      VARCHAR ( 100 ) NOT NULL );

INSERT t VALUES ( 1, 'aaa', 'bbb', 'ccc' );
COMMIT;

CREATE VARIABLE @ls_sql_new        LONG VARCHAR;
CREATE VARIABLE @ls_new_char_value LONG VARCHAR;
CREATE VARIABLE @ls_column_name    LONG VARCHAR;

CREATE TRIGGER tru_t BEFORE UPDATE ON t
REFERENCING OLD AS old_name NEW AS new_name
FOR EACH ROW
BEGIN

Set @ls_sql_new 
      = 'set @ls_new_char_value = convert(char(200), new_name.'
      + @ls_column_name 
      + ');' ;

MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_sql_new = "', @ls_sql_new, '"' ) TO CONSOLE;

Execute immediate @ls_sql_new;

END;

SET @ls_column_name = 'non_key_2';

UPDATE t 
   SET non_key_1 = 'xxx', 
       non_key_2 = 'yyy' 
 WHERE key_1 = 1;

MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_column_name    = "', @ls_column_name, '"' ) TO CONSOLE;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_new_char_value = "', @ls_new_char_value, '"' ) TO CONSOLE;

SET @ls_column_name = 'non_key_1';

UPDATE t 
   SET non_key_1 = 'ppp', 
       non_key_2 = 'qqq' 
 WHERE key_1 = 1;

MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_column_name    = "', @ls_column_name, '"' ) TO CONSOLE;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' @ls_new_char_value = "', @ls_new_char_value, '"' ) TO CONSOLE;

Here are the test results...

Adaptive Server Anywhere Database Engine Version 9.0.2.3575

Correlaton name 'new_name' not found
SQLCODE=-142, ODBC 3 State="42S02"

SQL Anywhere Personal Server Version 10.0.1.3415

DIAG 2010-11-07 05:26:16.390 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_2);"
DIAG 2010-11-07 05:26:16.406 @ls_column_name    = "non_key_2"
DIAG 2010-11-07 05:26:16.406 @ls_new_char_value = "yyy"
DIAG 2010-11-07 05:26:16.421 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_1);"
DIAG 2010-11-07 05:26:16.421 @ls_column_name    = "non_key_1"
DIAG 2010-11-07 05:26:16.437 @ls_new_char_value = "ppp"

SQL Anywhere Personal Server Version 11.0.1.2276

DIAG 2010-11-07 05:24:56.859 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_2);"
DIAG 2010-11-07 05:24:56.875 @ls_column_name    = "non_key_2"
DIAG 2010-11-07 05:24:56.875 @ls_new_char_value = "yyy"
DIAG 2010-11-07 05:24:56.890 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_1);"
DIAG 2010-11-07 05:24:56.890 @ls_column_name    = "non_key_1"
DIAG 2010-11-07 05:24:56.906 @ls_new_char_value = "ppp"

SQL Anywhere Personal Server Version 12.0.0.2589

DIAG 2010-11-07 05:23:06.593 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_2);"
DIAG 2010-11-07 05:23:06.609 @ls_column_name    = "non_key_2"
DIAG 2010-11-07 05:23:06.609 @ls_new_char_value = "yyy"
DIAG 2010-11-07 05:23:06.609 @ls_sql_new = "set @ls_new_char_value = convert(char(200), new_name.non_key_1);"
DIAG 2010-11-07 05:23:06.625 @ls_column_name    = "non_key_1"
DIAG 2010-11-07 05:23:06.625 @ls_new_char_value = "ppp"