Hybrid static/dynamic SQL

[dhkom]

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 "remote_server1_tablea" through "remote_server8_tablea".

You probably can see where this is going. We run the same query against each of the "remote_server<n>_tablea" proxy tables (joining in other database-specific proxy tables), accumulating all the results in a single local table which then drives our report.

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).

[awitter]

begin
  declare table_string varchar(100);
    --
    set table_string = 'mytable';
    --
    select * from `[table_string]`;
end

Be sure to use the correct quotes!!!

[dhkom]

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.