• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Need to "simulate" multiple full outer joins on db2

 
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do you have to do it in one query? Can you use a stored procedure?
 
K Dombroski
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Nice solution.
 
You learn how to close your eyes and tell yourself "this just isn't really happening to me." Tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic