Hi All,

I'm trying retrieve a column from a table where that column name is stored in another table.

Here is an example

Tab1
Col1, Col2
SomeData, Col1

Tab2
Col1, Col2
Hello, SomeData

select (select Col2 from Tab1 where Col1=SomeData) from Tab2
where Col2=SomeData

The sub query is only retrieving one row.

Here's the error I'm getting:

SQL10007N Message "4700" could not be retrieved. Reason code: "4".

Here's the version info:

Product: DB2 Connect Enterprise Edition
Version: DB2 v8.2
Fixpack: 7
OS: Windows XP Verison 5.1 SP3

Can this be done?

Any help much appreciated.

Cheers
Paul

Can you show the results you are expecting. I don't quite understand what you are trying to do.

So without the sub-select the query would look like this:

select Col1 from Tab2
where Col2=SomeData

Result: Hello

But I want to retrieve the column (i.e Col1 in this query) from another table (i.e. Tab1)

The column headings for Tab2 are stored in a column in another table

Please show the actual output you expect given a set of existing data in the two tables. Simply "describing" the output is not sufficient for me to understand what you are talking about.

I think I've described the problem quite well.

Thanks anyway.

@P4ul The answer is in here


----

I couldn't find a solution in that deck, so I'm assuming this isn't supported. Kinda makes sense. Thanks for help.

You'll need a two-stage process: one query whose output is a SELECT statement (with the "col1" in it), then execute that second query.
This can be achieved through either a .bat script with output redirect, or using SQL PL. Or just manually, of course, by passing the output of the first query to a second db2 run (by cut&paste into your command window).
Here is the query to generate the second SELECT statement:If Tab1 contains more than one row with 'SomeData' in its first column, this will generate all corresponding SELECT statements on Tab2.