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'

asked 11 Sep '23, 11:32

Baron's gravatar image

Baron
2.1k134145174
accept rate: 46%

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.

(11 Sep '23, 11:41) Volker Barth
Replies hidden

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

select 
(select list(`[fieldname]`) from `[fieldtable]`),
* from mystructure
(11 Sep '23, 13:21) Baron
2

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.

(11 Sep '23, 17:25) Chris Keating
1

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.

(12 Sep '23, 03:41) Volker Barth

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
permanent link

answered 12 Sep '23, 15:58

Baron's gravatar image

Baron
2.1k134145174
accept rate: 46%

edited 12 Sep '23, 16:05

1

That's a smart solution, methinks. :)

(13 Sep '23, 04:01) Volker Barth
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
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:

×69
×1

question asked: 11 Sep '23, 11:32

question was seen: 305 times

last updated: 13 Sep '23, 04:34