Topic
1 reply Latest Post - ‏2011-05-15T03:29:48Z by MatthiasNicola
jadeite100
jadeite100
1 Post
ACCEPTED ANSWER

Pinned topic IN DEPTH SQL QUESTION

‏2011-05-14T23:52:37Z |
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);
)
Updated on 2011-05-15T03:29:48Z at 2011-05-15T03:29:48Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    45 Posts
    ACCEPTED ANSWER

    Re: IN DEPTH SQL QUESTION

    ‏2011-05-15T03:29:48Z  in response to jadeite100
    Try this:

    SELECT *
    FROM ACCOUNT
    WHERE (PRODUCT_CODE, CURRENCY_CODE) IN (SELECT DISTINCT PRODUCT_CODE,CURRENCY_CODE FROM TABLE1)
    Depending on what you actually want to achieve you could also try the following, which can produce different results than the query above:

    SELECT *
    FROM ACCOUNT
    WHERE (PRODUCT_CODE) IN (SELECT DISTINCT PRODUCT_CODE FROM TABLE1)
    AND (CURRENCY_CODE) IN (SELECT DISTINCT CURRENCY_CODE FROM TABLE1)


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/