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.
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.
Joined: Dec 03, 2005
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.