indirect identifier within sql statement

[Baron]

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'

[VolkerBarth]

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.

[Baron]

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

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

[chris_keating]

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.

[VolkerBarth]

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.

[Baron · Accepted solution]

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

[VolkerBarth · Accepted solution]

That's a smart solution, methinks. 🙂

[Baron · Accepted solution]

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

Thanks again.