Data densification using Oracle Partitioned Outer Joins

I ran across a feature of Oracle 10g called Partitioned Outer Joins while tracking down the answer to another problem. Rather than get side tracked, I made a note to check them out at a later time and I’m glad I did. I frequently get specifications for reports where the data has gaps, but the report should be continuous, regardless of the presence of the data. The typical example is when you want a customer sales report, but some months a customer has no orders. Oracle-Developer.Net has a great article explaining how to create just such a query (and proper use of WITH). The first helpful piece of SQL is a WITH clause to generate a “time dimension” of continuous months

WITH year_months
AS (SELECT To_char(Add_months(DATE '2004-01-01',ROWNUM - 1), 'YYYYMM') AS year_month
FROM dual
CONNECT BY ROWNUM < 12)

Adding the Partition clause turns out to be quite straightforward:

SELECT co.name,
ym.year_month,
Nvl(Sum(co.amt),0) AS total_amount
FROM year_months ym
LEFT OUTER JOIN customer_orders co
PARTITION BY (co.name)
ON (To_char(co.dt,'YYYYMM') = ym.year_month)
GROUP BY co.name,
ym.year_month
ORDER BY co.name,
ym.year_month;