I had such a good response to my post of a few days ago ('IN search condition - expression-list represented by a variable'), that I thought I'd try asking another question that had been on my mind but was not particularly urgent. Following is a very simplified example of something I recently wished I could do: begin declare table_string char (100); -- set table_string = 'mytable'; -- select * from dyneval(table_string); end In the above, 'dyneval(...)' is my invention for a function that would evaluate the string as 'dynamic sql' and include the resulting text in the static SQL of it's context. Thus the select statement would resolve to: select * from mytable; A good question is, why would I ever want to do that? I had not come up with such a wish in my decades of writing SQL until recently. The recent circumstance is as follows: We have a customer that has a number of instances of our database, each with the same schema. We have created a separate 'multi-database reporting' database that has a remote server for each (CREATE SERVER statement). The multi-db reporting database has a set of proxy tables pointing to the same tables on each remote server. For example, we could have remote servers "server1" through "server8", and identically structured proxy tables You probably can see where this is going. We run the same query against each of the Thus, assuming we have a local table "remote_server" with the name of each remote server, we could write something like the following to accumulate the data from all databases: begin for for_remote_table as cur_remote_table dynamic scroll cursor for select server_name as s_server_name from remote_server do insert into accumlator_table ( col1, col2, etc ) select val1, val2, etc from dyneval ('remote_' || s_server_name || '_tablea') ; end for; end Right now we are repeatedly building a dynamic SQL string to add to the accumulator table, and doing "execute immediate". This works but is somewhat awkward. In my imagination, 'dyneval(...)' could be used to emit any part of a SQL statement - from clause, where clause, select list, etc. I think it could really simplify use of dynamic SQL - i.e. the only dynamic SQL is the parts that have to be dynamic. I'm interested in thoughts on this, and particularly whether there is a way I am missing to accomplish my end using SQL Anywhere capabilities already in place (hope I'm not missing something simple). |
Using v17, you can use indirect identifiers and/or TABLEREFs to parametrize SQL statements w.r.t. table and columns names and the like, without having to use dynamic SQL. See here: It's not as powerful as your fictional "dyneval()" function, if my understanding is correct, as it does not substitute complete clauses of a query - but you can parametrize identifiers within each part of a query, AFAIK, so I guess it should work fine. Fantastic! Thanks, Volker. This will definitely work for my need. I must say I still like my fictional "dyneval()" function for dynamic where clauses in static SQL. Maybe fiction will become reality some day?
(09 Dec '22, 11:00)
dharrel
Replies hidden
You can always suggest features in this forum and mark them with the "product-suggestion" tag, such as those: AFAIK, the SQL Anywhere engineers do read these postings. As just another customer, I don't know their priorities, obviously...
(09 Dec '22, 11:06)
Volker Barth
Good suggestion. I just edited my question to add that tag. I'll remember this going forward.
(09 Dec '22, 15:43)
dharrel
|
begin declare table_string varchar(100); -- set table_string = 'mytable'; -- select * from `[table_string]`; end Be sure to use the correct quotes!!! Thanks. This is, the "Indirect identifier" of the first answer - a feature new to SA17 that I was not aware of (but am now). I continue to think my suggestion is more flexible and powerful, but of course, am not aware of the possible complexities/complications. Perhaps it will be considered.
(12 Dec '22, 08:34)
dharrel
|