aspose file tools*
The moose likes JDBC and the fly likes In depth SQL question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "In depth SQL question" Watch "In depth SQL question" New topic
Author

In depth SQL question

John Smith
Ranch Hand

Joined: Aug 21, 2004
Posts: 48
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);
)

Darryl Burke
Bartender

Joined: May 03, 2008
Posts: 4523
    
    5

John, please BeForthrightWhenCrossPostingToOtherSites.
http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14617058
http://www.dbforums.com/db2/1666467-depth-sql-question.html (answered here)


luck, db
There are no new questions, but there may be new answers.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

Your statement 3 is a good attempt, but not completely correct.


If your table1 has multiple entries for the same pair of a product_code & currency_code combination, you could use 'where exists':


If the combination product_code & currency_code is unique in table1, then you can use a simple inner join


If it is guaranteed that you always have the product_code & currency_code combinations from table2 available in table1, you can just run the query without using table1


OCUP UML fundamental and ITIL foundation
youtube channel
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

There you go! All my effort for nothing.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: In depth SQL question
 
Similar Threads
some questions about EJBQL
using prepare statements
Mapping for Table without Primary Key
Preparing a PreparedStatement
multiple Select doesn't work.