Can you use a Partitioned Outer Join to Fill Gaps in Sparse Data in DB2

Angela_Wilcox
Angela_Wilcox used Ask the Experts™
on
In Oracle you can use a special type of outer join - a partitioned outer join - to fill gaps in sparse data. Is this function available in DB2?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Angela,

Haven't seen you in a while.....

DB2 supports query partitioning.  There are a lot of partitioning options.

If you'll give me a hint as to what you want, I'll be gald to help write the query.


Kent

Author

Commented:
Hi Kent!  Yeah I got out of Unix world for awhile but now I have returned working for JCPenney.  I do not have the exact SQL - I am part of a task group... trying to resolve an issue.  We have a fact table with store, product, week, and BOH inventory.  This of course gets joined out to a time, store, and product dimension table.  So let's say - we have store join during week 3 - and there are not records for week 1, 2 in the database.  The oracle partition join deal would let us fill in these missing weeks with a zero when we query.  How do you write something like that in DB2?  Is that too pie in the sky?  We still need to go out for pizza.

Author

Commented:
Kent - here is the SQL:
select T55750."PROD_DIV_NB" as c2,
     T36046."LOC_NB" as c3,
     T36046."LOC_KY" as c4,
     T35974."FIS_WK_KY" as c5,
     sum(T73238."BOH_QTY") as c7
from
     "JOMADM"."DIM_REPORTING_LOC" T36046,
     "JOMADM"."DIM_PROD_DIV" T55750,
     "JOMADM"."DIM_FIS_WK" T35974,
     "JOMADM"."MQT_FCT_STR_DIV_ACT" T73238
where  ( T35974."FIS_WK_KY" = T73238."FIS_WK_KY" and T36046."LOC_KY" = T73238."LOC_KY" and T35974."FY_NB" = 2008 and T36046."DISTRICT_NB" = 8413 and T55750."PROD_DIV_NB" = T73238."PROD_DIV_NB" and T55750."PROD_DIV_NB" = 9 and T73238."PROD_DIV_NB" = 9 )
group by T35974."FIS_WK_KY", T36046."LOC_KY", T36046."LOC_NB", T55750."PROD_DIV_NB"
order by c2, c4, c5

So for any LOC and DIV that has a record but has a missing week - we would want the missing week to return a 0.
Clear??
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

I'm not aware of DB2 filling in missing values with a PARTITION clause.

But since you've got a time based dimension, it would seem pretty easy to structure the query to outer join to time.


And yeah -- pizza would be good.  Where are you these days?

Kent

Author

Commented:
Plano Texas... Well a outer join on time will not work as it just creates a single record...   not a record for each valid LOC DIV combination.  This is such a bugger.
I'm confused.

If you have a time dimension, you should be able to outer join to it and create at least one row for each time unit.

If it's a business requirement that "empty periods" be reported on, it may be a requirement to structure the data in the time dimension to cover each period, not just those with value rows in the fact table.

How many periods do you want in the report and what's the increment?

Kent

Author

Commented:
We have weeks through year with months qtrs and half years.

So that is what I thought too - regarding the fact that the outer join should return a row.  But our DBA says that if we do that it will just create a single record for the missing records for the first combaination of LOC and DIV not for every missing row as what we want.

Ok, so it would seem that there must be missing rows in the table that sources LOC and/or DIV.  Still, if the correct date rows are selected from the time dimension (and they all exist) outer joining to the rows of the time table will give you rows for that time period (though the non-date values may be NULL).

Much of this query, it would seem, can be done easily with the DB2 OLAP extensions.  CUBES, CUBE GROUPS, etc.

  http://www.inf.uni-konstanz.de/dbis/teaching/ws0405/information-systems/tutorial-04.pdf

That document is about 20 pages of easy reading, with the last few pages incredibly informative.  :)

Kent


Author

Commented:
Thanks Kent - I forgot to update this

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial