Need to "simulate" multiple full outer joins on db2

[K Dombroski]

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:

Select c.Element1Number,c.Element2Number,c.Element3Number,c.Element4Number,c.Element5Number,c.Element6Number,
c.description as AccountDescription,c.AccountTypeCode, b1.amount as Budget1Amount, b2.amount as Budget2Amount, 
g3.Month1CashBasisAmt as Column3Month1,g3.Month2CashBasisAmt as Column3Month2,g3.Month3CashBasisAmt as Column3Month3,
g3.Month4CashBasisAmt as Column3Month4, g3.Month5CashBasisAmt as Column3Month5,g3.Month6CashBasisAmt as Column3Month6,
g3.Month7CashBasisAmt as Column3Month7,g3.Month8CashBasisAmt as Column3Month8, g3.Month9CashBasisAmt as Column3Month9,
g3.Month10CashBasisAmt as Column3Month10,g3.Month11CashBasisAmt as Column3Month11,g3.Month12CashBasisAmt as Column3Month12, 
g4.Month1CashBasisAmt as Column4Month1,g4.Month2CashBasisAmt as Column4Month2,g4.Month3CashBasisAmt as Column4Month3,
g4.Month4CashBasisAmt as Column4Month4, g4.Month5CashBasisAmt as Column4Month5,g4.Month6CashBasisAmt as Column4Month6,
g4.Month7CashBasisAmt as Column4Month7,g4.Month8CashBasisAmt as Column4Month8, g4.Month9CashBasisAmt as Column4Month9,
g4.Month10CashBasisAmt as Column4Month10,g4.Month11CashBasisAmt as Column4Month11,g4.Month12CashBasisAmt as Column4Month12, 
a.FirstNumberInRange, a.LastNumberInRange,a.description as RangeDescription From ChartOfAccounts c  

FULL OUTER JOIN BudgetDetails b1 on (c.id = b1.accountId and b1.budgetheaderid = '46') 
FULL OUTER JOIN BudgetDetails b2 on (c.id = b2.accountId and b2.budgetheaderid = '47') 
FULL OUTER JOIN GLSummary g3 on (c.id = g3.AccountId and year(g3.budgetStartDate) = '2004') 
FULL OUTER JOIN GLSummary g4 on (c.id = g4.AccountId and year(g4.budgetStartDate) = '2005') 

LEFT OUTER JOIN accountrangeoptions a on ((ElementPositionNumber = '5' and (c.Element5Number between a.FirstNumberInRange and a.LastNumberInRange))) 
where (c.AccountTypeCode = '5' or c.AccountTypeCode = '6') and (c.StatusCode <> 'D')  AND b2.Amount <> '0' 
AND (c.element1Number >= 0 and c.element1Number <= 999999999) AND (c.element2Number >= 0 and c.element2Number <= 999999999) 
order by c.Element1Number,c.Element2Number,c.Element3Number,c.Element4Number,c.Element5Number,c.Element6Number

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]

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

[K Dombroski]

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]

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]

Nice solution.