cancel
Showing results for 
Search instead for 
Did you mean: 

indirect identifier within sql statement

Baron
Participant
707

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'

Accepted Solutions (1)

Accepted Solutions (1)

Baron
Participant

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
Contributor

That's a smart solution, methinks. 🙂

Baron
Participant
0 Kudos

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

Thanks again.

Answers (0)