Showing posts with label oracle sql partioned outer join. Show all posts
Showing posts with label oracle sql partioned outer join. Show all posts

Friday, January 7, 2011

Partitioned outer join in Oracle 10 and over

This is really an amazing thing. you got to report on data which is not in the database! yes i mean it. one of my report query shows counts grouping by on member and month of the year. something like this,
ABC 01-2001 54
ABC 02-2001 45
ABC 04-2001 12
DEF 09-2001 76
DEF 10-2001 65
you can see that there is no data for ABC for the month of march. this is what the QC team here needs to catch. what i was asked to do is to display another row like ABC 03-2001 0.
well my current query is 40 lines with two subqueries outerjoined. its complicated and i have to make it over complicated now! i tried several nasty ways and was not even close to what was needed. then googled and found this article..

http://www.oracle.com/technetwork/issue-archive/o14asktom-084433.html

this guy is doing exactly what i needed. but i could hardly grasp his explanation.
meanwhile i shared my issue with one of the DBA and she was also scratching her head for some time now. she suggested few techniques and after a while forwarded me this artcile, which i feel is worth sharing.

http://www.oracle-developer.net/display.php?id=312

this is what i need and its explained very clearly.

i am back to work trying to mess around with my query now!