Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Select statement in DB2

    I have 2 tables: TAB1 incliudes PRIMARY_PROD_NO and ALT_PROD_NO
    TAB2 has PROD_NO (as KEY) and PROD_DESCRIPTION.

    How do I code a select statement that will give the result set of:

    PRIMARY_PROD_NO, description, ALT_PROD_NO, Description?

    Eachh Description relating to each Prod_no.

    thank you
    IPPOPP

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Tables

    Hi,

    From what I can say it's a simple select, however to put a proper answer we need the tables and if there is a relation.

    What I suggest is you use the sql assist function in the command center to help you to the needful.

    Please post the above details and we can give you the sql statement.

    Regards

    Nitin.
    HTH

    Nitin

    Ask the experienced rather than the learned

  3. #3
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Hi

    Have you tried:

    SELECT PRIMARY_PROD_NO, A.description, ALT_PROD_NO,
    B.Description
    FROM TAB1
    INNER JOIN TAB2 A
    ON PRIMARY_PROD_NO = A.PROD_NO
    INNER JOIN TAB2 B
    ON ALT_PROD_NO = B.PROD_NO

  4. #4
    Join Date
    Nov 2003
    Location
    Jakarta, Indonesia
    Posts
    32
    Another version:

    SELECT PRIMARY_PROD_NO, (SELECT DESCRIPTION FROM TAB2 WHERE PROD_NO=PRIMARY_PROD_NO) AS PRIMARY_DESCRIPTION, ALT_PROD_NO, (SELECT DESCRIPTION FROM TAB2 WHERE PROD_NO=ALT_PROD_NO) AS ALT_DESCRIPTION FROM TAB1

    Originally posted by Walter Janissen
    Hi

    Have you tried:

    SELECT PRIMARY_PROD_NO, A.description, ALT_PROD_NO,
    B.Description
    FROM TAB1
    INNER JOIN TAB2 A
    ON PRIMARY_PROD_NO = A.PROD_NO
    INNER JOIN TAB2 B
    ON ALT_PROD_NO = B.PROD_NO
    Regards,
    Adam Ac

  5. #5
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Hi adam_ac

    Your version is not possible for DB2 z/OS up to Version 7. V8 supports this syntax, but only if the select in the select list returns a scalar value.

Posting Permissions

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