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

asked 09 Dec '22, 09:43

dharrel's gravatar image

dharrel
19081019
accept rate: 0%

edited 09 Dec '22, 15:42


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.

permanent link

answered 09 Dec '22, 10:04

Volker%20Barth's gravatar image

Volker Barth
39.2k353537806
accept rate: 33%

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!!!

permanent link

answered 12 Dec '22, 04:01

ArcoW's gravatar image

ArcoW
718
accept rate: 0%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×112
×9

question asked: 09 Dec '22, 09:43

question was seen: 180 times

last updated: 12 Dec '22, 08:34