dynamically created column names

[Michael Gruner]

Hi,

is there any idea about creating column names dynamically within a
select statement and without stored procedures?

We have some tables that have column names that consist of chars and
numbers i.e.:
Col0001, Col0002... Now I would like to have a function that returns
the column name Col0001 if i call that function i.e. myfunc(1).

I know it is possible with concat(Col000,myVariable) but DB2 does not
recognice the return value as a column name.

thanks
Michael

[Serge Rielau]

Michael Gruner wrote:

Hi,

is there any idea about creating column names dynamically within a
select statement and without stored procedures?

We have some tables that have column names that consist of chars and
numbers i.e.:
Col0001, Col0002... Now I would like to have a function that returns
the column name Col0001 if i call that function i.e. myfunc(1).

I know it is possible with concat(Col000,myVariable) but DB2 does not
recognice the return value as a column name.

thanks
Michael

The only way to do this is to use dynamic SQL, first execute myfunc()
then compose the select.
SQL as a language is incapable of this. Come to think of it I know only
very few languages that could possibly do this. LISP being on of the.

Cheers
Serge

[Knut Stolze]

Serge Rielau wrote:

Michael Gruner wrote:

Hi,

is there any idea about creating column names dynamically within a
select statement and without stored procedures?

We have some tables that have column names that consist of chars and
numbers i.e.:
Col0001, Col0002... Now I would like to have a function that returns
the column name Col0001 if i call that function i.e. myfunc(1).

I know it is possible with concat(Col000,myVariable) but DB2 does not
recognice the return value as a column name.

What do you need that for? Maybe if we understand the reason for the
request, we could give you some advice.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Michael Gruner]

Knut Stolze schrieb:

What do you need that for? Maybe if we understand the reason for the
request, we could give you some advice.

O.K., we have an AS400 that serves a business application. We'd like to
fill some reports with the data retrieved from a DB2 that accesses the
files that were stored by the application.

The table visible through DB2 is a "one row" table with 365 + 365 + x
columns. That 365 + 365 colums have a static part as well as a dynamic
part, i.e.: abc001 and def001. We now would like to only call a function
with the dynamic part returning abc001 and def001 so we can use it as
column names within a select-statement.

Any ideas?
Michael