Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    10

    Unanswered: Subselect in a SELECT

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Can you show the results you are expecting. I don't quite understand what you are trying to do.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2011
    Posts
    10
    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)

  4. #4
    Join Date
    Jul 2011
    Posts
    10
    The column headings for Tab2 are stored in a column in another table

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by P4ul View Post
    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)
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jul 2011
    Posts
    10
    I think I've described the problem quite well.

    Thanks anyway.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    @P4ul The answer is in here


    ----
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jul 2011
    Posts
    10
    I couldn't find a solution in that deck, so I'm assuming this isn't supported. Kinda makes sense. Thanks for help.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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:
    Code:
    SELECT 'select '|| Col2 ||' from Tab2 where Col2='''|| Col1 || ''';'
    FROM Tab1 WHERE Col1='SomeData'
    If Tab1 contains more than one row with 'SomeData' in its first column, this will generate all corresponding SELECT statements on Tab2.
    Last edited by Peter.Vanroose; 07-16-11 at 08:35.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •