File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Need to Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Need to "simulate" multiple full outer joins on db2" Watch "Need to "simulate" multiple full outer joins on db2" New topic
Author

Need to "simulate" multiple full outer joins on db2

K Dombroski
Ranch Hand

Joined: Dec 03, 2005
Posts: 39
Found a page (http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbafymst124.htm) with how to "simulate" full outer join on db2 (because for who knows why, db2 doesn't support full outer joins and thinks it's a good idea to force developers to double their code to get the same results...??)
Unfortunately, I have a dynamic query which may have anywhere from 1 to 5 full outer joins. Here is an example of a query that runs on sql server:
How on earth do I fake this to get the same results in DB2?? Everything I have tried to date has resulted in errors about undefined tables.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

Do you have to do it in one query? Can you use a stored procedure?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
K Dombroski
Ranch Hand

Joined: Dec 03, 2005
Posts: 39
Unfortunately, yes, it has to be done in a single query, or at least, I need a single result set containing all of that data, as the combined data needs to go into a single record (i.e. one row needs to contain Budget1Amount, Budget2Amount, etc). In addition, this query is highly dynamic, as there can be anywhere from 1 to 5 "columns" where the user can request budget amounts, year amounts, or estimated amounts, and the MonthXCashBasisAmount fields only apply if the user is requesting a year amount, etc. The code to make up this query takes about 175 lines of code because of all the if statements and for loops necessary (and will require more in order to check which database system is in use and do the necessary union/exclusion joins). Right now, though, if I can just figure out how to make this query work in db2, I can figure out how to make the dynamic part work later. Piece-mealing it together would be fine, so long as I can end up with a single result set that contains all the data I need for inserting into the table for the report.
K Dombroski
Ranch Hand

Joined: Dec 03, 2005
Posts: 39
Just an fyi, I ended up completely rewriting the query using left outer joins with the "ChartOfAccounts" table as the left one, since that is the data that I need in every record, regardless of what's in the others; it's now 4+ times as long as it was, but it works on both systems. I would post it, but it's really huge. Basically, I'm taking query results of table c and left outer joining with entire query again on b1, left outer joining that with the entire query again on b2, etc, with the where and order by at the end. Thanks to Jeanne for giving me the idea with the multiple query suggestion.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

Nice solution.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need to "simulate" multiple full outer joins on db2