Question Re: Hybrid static/dynamic SQL in Technology Q&A

Hybrid static/dynamic SQL

dhkom — Fri, 09 Dec 2022 09:43:19 GMT

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

Re: Hybrid static/dynamic SQL

VolkerBarth — Fri, 09 Dec 2022 10:04:48 GMT

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.

Re: Hybrid static/dynamic SQL

dhkom — Fri, 09 Dec 2022 11:00:17 GMT

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?

Re: Hybrid static/dynamic SQL

VolkerBarth — Fri, 09 Dec 2022 11:06:57 GMT

You can always suggest features in this forum and mark them with the "product-suggestion" tag, such as those:

https://sqlanywhere-forum.sap.com/tags/product-suggestion/

AFAIK, the SQL Anywhere engineers do read these postings. As just another customer, I don't know their priorities, obviously...

Re: Hybrid static/dynamic SQL

dhkom — Fri, 09 Dec 2022 15:43:27 GMT

Good suggestion. I just edited my question to add that tag. I'll remember this going forward.

Re: Hybrid static/dynamic SQL

awitter43 — Mon, 12 Dec 2022 04:01:31 GMT

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

Be sure to use the correct quotes!!!

Re: Hybrid static/dynamic SQL

dhkom — Mon, 12 Dec 2022 08:34:06 GMT

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.