how to use variable table name in select query (Advantage Data Architect)

Question

I'm writing the same small sql script and I'm using Advantage Data Architect 11.10.

One part of my script is create 'cursor' based on the following request:

...
declare myCursor;
declare prefix char(4);
declare tableName char(10);
...
tableName = prefix + '_table1';
open myCursor as select * from tableName;
...

When I run my script, I get the following error:

poQuery: Error 7200: AQE Error: State = HY000; NativeError = 7041; [iAnywhere Solutions][Advantage SQL][ASA] Error 7041: File not found. Verify the specified path and file name is correct. Table name: tableName ** Script error information: -- Location of error in the SQL statement

I understood that mean this error message and I tried to find answer in the Internet.

One of the way to resolve my problem is created dynamically sql.

I found some examples how to use the dynamically sql in Advantage Data Architect but this examples does not work. I created the following code:

...
declare @test string;
...
set @test = 'open myCursor as select * from ' + tableName;
execute(@test);
...

When I run my script, I get the following error:

Expected lexical element not found: Expecting keyword PROCEDURE, IMMEDIATE, DPRINT or PRINT after the EXECUTE keyword. -- Location of error in the SQL statement is:

And I would like to ask you, may be anybody know how to resolve my problem?

Thank you.

Sorry for my English.

Jens Mühlenhoff · Accepted Answer · 2014-11-06T18:06:37.717

The syntax for executing dynamic SQL in ADS is:

DECLARE @my_table TEXT;

@my_table = 'abc';

EXECUTE IMMEDIATE 'DELETE FROM ' + @my_table;

If you want to use a cursor inside dynamic SQL you have to declare the cursor inside the string that you want to execute:

DECLARE @sql TEXT;
DECLARE @crlf TEXT;

@crlf = CHAR(13) + CHAR(10);    

@sql =   'DECLARE cursor1 CURSOR AS SELECT * FROM ' + @mytablename + ';' + @crlf
       + 'DECLARE @maxid Integer;' + @crlf
       + '' + @crlf
       + 'OPEN cursor1;' + @crlf
       + '@maxid = 0;' + @crlf
       + '' + @crlf
       + 'WHILE FETCH cursor1 DO' + @crlf
       + '  IF cursor1.id > @maxid THEN' + @crlf
       + '   @maxid = cursor1.id;' + @crlf
       + '  END IF;' + @crlf
       + 'END WHILE;' + @crlf
       + '' + @crlf
       + 'CLOSE cursor1;' + @crlf
       + '' + @crlf;

EXECUTE IMMEDIATE @sql;

If you want to work with results produced inside the immediate SQL on the outside you have to store your results in some table(s).

Another alternative would be to use stored procedures, stored functions, etc.

how to use variable table name in select query (Advantage Data Architect)

1 Answers1