|
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' |
|
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 Replies hidden
Actually working with array-type in SQL is (for me) always a difficult task, sothat switched to this solution (shorter and clearer). Thanks again.
(13 Sep '23, 04:34)
Baron
|
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.
Thanks for the hint, but even with backtick I get a syntax error:
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.
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.