SEO

#1 (**permalink**) 02-27-2008, 02:29 AM

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

#2 (**permalink**) 02-27-2008, 02:29 AM

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

#3 (**permalink**) 02-27-2008, 02:30 AM

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

#4 (**permalink**) 02-27-2008, 02:34 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

dynamically created column names