Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    11

    Unanswered: In depth sql question

    Hi All:

    I am currently using Ibm Db2 8.2 for mainframe.

    Is it possible to combine statement1 and statement2 similar to statement 3.
    Basically I want to use the sql result in statement 1 for product_code and currency_code and placed it in a where clause for statement 2
    STATEMENT 1:
    SELECT DISTINCT PRODUCT_CODE,CURRENCY_CODE FROM TABLE1;

    STATEMENT 2:
    SELECT * FROM ACCOUNT WHERE PRODUCT_CODE=? AND CURRENCY_CODE=?

    STATEMENT 3:
    SELECT * FROM ACCOUNT WHERE PRODUCT_CODE=? AND CURRENCY_CODE=? (SELECT DISTINCT PRODUCT_CODE,CURRENCY_CODE FROM TABLE1);
    )

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    jadeite100, Here are a few ways. There are probably others:
    Code:
    SELECT * 
    FROM ACCOUNT 
    WHERE (PRODUCT_CODE, CURRENCY_CODE) IN(SELECT PRODUCT_CODE, CURRENCY_CODE 
                                           FROM TABLE1
                                          )
    Code:
    SELECT * 
    FROM ACCOUNT 
    WHERE EXISTS (SELECT *
                  FROM TABLE1
                  WHERE ACCOUNT.PRODUCT_CODE  = TABLE1.PRODUCT_CODE
                    AND ACCOUNT.CURRENCY_CODE = TABLE1.CURRENCY_CODE
                )
    Code:
    SELECT ACCOUNT.*
    FROM ACCOUNT
           INNER JOIN
         (SELECT DISTINCT PRODUCT_CODE, CURRENCY_CODE
          TABLE1
         ) AS NEST_TAB
           ON     ACCOUNT.PRODUCT_CODE  = NEST_TAB.PRODUCT_CODE
              AND ACCOUNT.CURRENCY_CODE = NEST_TAB.CURRENCY_CODE

  3. #3
    Join Date
    May 2009
    Posts
    11

    Thank You!!!!

    Hi:

    Thank you for your help!!!
    You really save my day!!!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another variation(Just for fun.):

    Code:
    SELECT ACCOUNT.*
     FROM  ACCOUNT
     CROSS JOIN
           LATERAL
           (SELECT DISTINCT
                   0
             FROM  TABLE1 T1
             WHERE T1.PRODUCT_CODE  = ACCOUNT.PRODUCT_CODE
               AND T1.CURRENCY_CODE = ACCOUNT.CURRENCY_CODE
           ) AS NEST_TAB
    Last edited by tonkuma; 05-19-11 at 09:56. Reason: Change select list of nested table expression.

Posting Permissions

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