How to use a table variable name in a selected query (Advantage Data Architect)
I am writing the same little sql script and I am using Advantage Data Architect 11.10.
One part of my script creates a "cursor" based on the following query:
...
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: Error AQE: Status = HY000; NativeError = 7041; [iAnywhere Solutions] [Advantage SQL] [ASA] Error 7041: File not found. Check if the specified path and file name are correct. Table name: tableName ** script error information: - Location of error in SQL statement
I realized that this was an error message and I tried to find an answer on the internet.
One way to solve my problem is creating dynamically sql.
I found some examples of using dynamic sql in Advantage Data Architect, but these examples don't 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:
The expected lexical element was not found: Waiting for the keyword PROCEDURE, IMMEDIATELY, DPRINT or PRINT after the keyword EXECUTE. - Location of the error in the SQL statement:
And I would like to ask you, maybe someone knows how to solve my problem?
Thank.
Sorry for my English.
source to share
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 need to declare the cursor inside the line 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 obtained from inside the raw SQL from outside, you need to store the results in some tables.
Another alternative might be to use stored procedures, stored functions, etc.
source to share