Question Re: indirect identifier within sql statement in Technology Q&A

indirect identifier within sql statement

Baron — Mon, 11 Sep 2023 11:32:03 GMT

I have the following 3 tables with contents as follows:

create or replace table mystructure (fieldname varchar(100), fieldtable varchar (100), hinttext varchar (100));
create or replace table mydata1 (dataindex1 int, datacontent1 varchar(100));
create or replace table mydata2 (dataindex2 int, datacontent2 varchar(100));

insert into mystructure values 
('datacontent1', 'mydata1', 'hint1'),
('datacontent2', 'mydata2', 'hint2');

insert into mydata1 
values (1, 'data1_1'),
values (2, 'data1_2'),
values (3, 'data1_3');

insert into mydata2
values (1, 'data2_1'),
values (2, 'data2_2'),
values (3, 'data2_3');

Why I get a syntax error while executing this select statement?

select 
(select list('[fieldname]') from '[fieldtable]'),
* from mystructure

What I want to get is a list of available values of both tables mydata1 & mydata2 besides the main table mystructure, so something like this:

'data1_1,data1_2,data1_3';'datacontent1';'mydata1';'hint1'
'data2_1,data2_2,data2_3';'datacontent2';'mydata2';'hint2'

Re: indirect identifier within sql statement

VolkerBarth — Mon, 11 Sep 2023 11:41:02 GMT

Indirect iedentifiers require back quotes, not single quotes. Compare this older FAQ you may remember...

If you want to select data from several known tables with compatible schema, a UNION ALL might also do the trick.

Re: indirect identifier within sql statement

Baron — Mon, 11 Sep 2023 13:21:02 GMT

Thanks for the hint, but even with backtick I get a syntax error:

select 
(select list(`[fieldname]`) from `[fieldtable]`),
* from mystructure

Re: indirect identifier within sql statement

chris_keating — Mon, 11 Sep 2023 17:25:05 GMT

A result set column is not a variable and as such cannot be used as a reference to an indirect identifier. I am not sure what syntax error you are encountering but I do get the error Variable 'fieldtable' not found. This is expected as that variable is not declared.

Re: indirect identifier within sql statement

VolkerBarth — Tue, 12 Sep 2023 03:41:14 GMT

In addition to Chris's statement, as you need variables as value for an indirect identifier, you need to supply the value "one by one", i.e. when using a table to supply the values, you need to do this on a per-row base.

Here's a sample with a FOR statement that uses the contents of your "mystructure" table for each row as input for indirect identifiers.

begin
   for forCrs as crs cursor for
      select fieldname, fieldtable from mystructure order by 1
   for read only
   do
      select list(`[fieldname]`) from `[fieldtable]`;
   end for;
end;

Note, the cursor does not use aliases for the column names but within the do/end for block, the names "fieldname" and "fieldtable" are not column names but the automatically declared cursor variables. I could also have used aliases:

begin
   for forCrs as crs cursor for
      select fieldname as varFieldname, fieldtable as varFieldtable from mystructure order by 1
   for read only
   do
      select list([varFieldname]) from [varFieldtable];
   end for;
end;

Aside: The FOR statement does return the contents of each table separately, in contrast to your requirement. You might be able to use ARRAY-type variables to store a variable number of values for your use case.

Re: indirect identifier within sql statement

Baron — Tue, 12 Sep 2023 15:58:46 GMT

Thanks for the replies and hints, it has worked after adding a help procedure like this:

create or replace procedure MyHelpProc(table_name varchar(100), col_name varchar(100))
result (myres varchar(100))
begin
select list(`[col_name]`) from `[table_name]`;
end;
----
select (select * from MyHelpProc(fieldtable, fieldname)), fieldname, fieldtable, hinttext from mystructure

Re: indirect identifier within sql statement

VolkerBarth — Wed, 13 Sep 2023 04:01:05 GMT

That's a smart solution, methinks. 🙂

Re: indirect identifier within sql statement

Baron — Wed, 13 Sep 2023 04:34:16 GMT

Actually working with array-type in SQL is (for me) always a difficult task, sothat switched to this solution (shorter and clearer).

Thanks again.