Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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 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:


asked 11 Sep '23, 11:32

Baron's gravatar image

accept rate: 48%

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 list(`[fieldname]`) from `[fieldtable]`),
* from mystructure
(11 Sep '23, 13:21) Baron

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

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.

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

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:

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

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))
select list(`[col_name]`) from `[table_name]`;
select (select * from MyHelpProc(fieldtable, fieldname)), fieldname, fieldtable, hinttext from mystructure
permanent link

answered 12 Sep '23, 15:58

Baron's gravatar image

accept rate: 48%

edited 12 Sep '23, 16:05


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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 11 Sep '23, 11:32

question was seen: 425 times

last updated: 13 Sep '23, 04:34