Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: Help on Corelated sub query

    I need to know whether thi stype of subquery is allowed in DB2 or not, in general in Oracel or SQl server we use

    Select field1, (select field2 from tableb where tableB.field1 = tablesA.field1) from Table A

    Is this is possible in DB2,
    I tried in QMF but it is gigving SQL error

    Or please let me know an alternative way

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 does allow for correlated sub-queries. But this looks like a regular join to me.

    Select tablea.field1, tableb.field2
    from tablea, tableb
    where tableb.field1 = tablesa.field1
    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
    Jan 2004
    Posts
    3
    Originally posted by Marcus_A
    DB2 does allow for correlated sub-queries. But this looks like a regular join to me.

    Select tablea.field1, tableb.field2
    from tablea, tableb
    where tableb.field1 = tablesa.field1
    Thanks Marcus,

    But , i have given just an example, i need to dispaly fileds in columns wise, where it is actually in row wise.

    ex

    Table a

    fld1 fld2
    x y


    Table b
    fld1 fld2
    x 1
    x 2
    x 3

    i want to display

    x y 1 2 3

    where tablea.x = tableb.x


    but the tableb may or may not have all the three rows

    Could you give me a solution

    Also let me know how to use a correlated subquery, i have mentioned in my first post in DB2

  4. #4
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    In this case I suggest on outer join

    Select tablea.field1, tableb.field2
    from tablea
    LEFT OUTER JOIN tableb
    ON tableb.field1 = tablesa.field1

  5. #5
    Join Date
    Jan 2004
    Posts
    3
    Originally posted by Walter Janissen
    In this case I suggest on outer join

    Select tablea.field1, tableb.field2
    from tablea
    LEFT OUTER JOIN tableb
    ON tableb.field1 = tablesa.field1
    Thanks walter,

    even in this case i wont get the values in column wise, it will display in row wise only

  6. #6
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    OK, when I look at your syntax, you coded at first, i.e. a select in between a select-list, then you have to wait for DB2 z/OS version 8. But even then, this select may only retrieve a single row.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think QMF may have some FORM's functions to help you with this.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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