Tags
This posts describes a solution to a problem posted on the ORACLE SQL and PL/SQL forums.
Problem:
The original poster (OP) has a table which has monthly data for agents.
WITH agt_dum(agent_id,mon,commission)
AS (SELECT 1, 1, 50 FROM dual UNION ALL
SELECT 1, 2, 100 FROM dual UNION ALL
SELECT 1, 6, 25 FROM dual UNION ALL
SELECT 2, 1, 10 FROM dual UNION ALL
SELECT 2, 2, 20 FROM dual)
SELECT *
FROM agt_dum
/
AGENT_ID MON COMMISSION
---------- ---------- ----------
1 1 50
1 2 100
1 6 25
2 1 10
2 2 20
as you can see not every month has data for every AGENT_ID and the OP needed to have a query result like:
AGENT_ID MON COMMISSION
---------- ---------- ----------
1 1 50
1 2 100
1 3 0
1 4 0
1 5 0
1 6 25
1 7 0
1 8 0
1 9 0
1 10 0
1 11 0
1 12 0
2 1 10
2 2 20
2 3 0
2 4 0
2 5 0
2 6 0
2 7 0
2 8 0
2 9 0
2 10 0
2 11 0
2 12 0
meaning for each month an entry for each AGENT_ID and if no commission exists (no row exist) a 0 value for that specific column.
Solution(s):
The first thing that I though about was to have an outer join on the AGENT_ID. The driving table would be something that has all months (month numbers) from 1 to 12.
Outer Join with a “ALL_MONTHS” source:
WITH all_months (mon)
AS (SELECT rownum AS mon
FROM dual
CONNECT BY rownum <= 12)
SELECT mon
FROM all_months
/
MON
---------
1
2
3
4
5
6
7
8
9
10
11
12
This source could be used as the driving table in an outer join query
WITH agt_dum(agent_id,mon,commission)
AS (SELECT 1, 1, 50 FROM dual union all
SELECT 1, 2, 100 FROM dual union all
SELECT 1, 6, 25 FROM dual union all
SELECT 2, 1, 10 FROM dual union all
SELECT 2, 2, 20 FROM dual)
,all_months (mon) AS (SELECT ROWNUM
FROM dual
CONNECT BY ROWNUM <= 12)
SELECT ad.agent_id, am.mon, NVL(ad.commission,0) AS commission
FROM all_months am
LEFT OUTER JOIN agt_dum ad ON (ad.mon = am.mon)
ORDER by ad.agent_id, am.mon
/
But a simple outer join will no solve the problem, as all the outer joined columns will be NULL…
AGENT_ID MON COMMISSION
---------- ---------- ----------
1 1 50
1 2 100
1 6 25
2 1 10
2 2 20
3 0
4 0
5 0
7 0
8 0
9 0
10 0
11 0
12 0
As you can see you get an “empty” row for all months that are not available with any AGENT_ID … but what OP wanted was to have one row for every month for every AGENT_ID.
Partitioned Outer Join
So the solution for that is having a partitioned outer join like:
WITH agt_dum(agent_id,mon,commission)
AS (SELECT 1, 1, 50 FROM dual union all
SELECT 1, 2, 100 FROM dual union all
SELECT 1, 6, 25 FROM dual union all
SELECT 2, 1, 10 FROM dual union all
SELECT 2, 2, 20 FROM dual)
,all_months (mon) AS (SELECT ROWNUM
FROM dual
CONNECT BY ROWNUM <= 12)
SELECT ad.agent_id, am.mon, NVL(ad.commission,0) AS commission
FROM all_months am
LEFT OUTER JOIN agt_dum ad PARTITION BY (ad.agent_id) ON (ad.mon = am.mon)
ORDER by ad.agent_id, am.mon
/
Using this query, ORACLE does an OUTER JOIN for every AGENT_ID in the AGT_DUM view and the result is exactly what OP wanted.
AGENT_ID MON COMMISSION
---------- ---------- ----------
1 1 50
1 2 100
1 3 0
1 4 0
1 5 0
1 6 25
1 7 0
1 8 0
1 9 0
1 10 0
1 11 0
1 12 0
2 1 10
2 2 20
2 3 0
2 4 0
2 5 0
2 6 0
2 7 0
2 8 0
2 9 0
2 10 0
2 11 0
2 12 0
Additional solutions:
Model Clause:
WITH agt_dum(agent_id,mon,commission)
AS (SELECT 1, 1, 50 FROM dual UNION ALL
SELECT 1, 2, 100 FROM dual UNION ALL
SELECT 1, 6, 25 FROM dual UNION ALL
SELECT 2, 1, 10 FROM dual UNION ALL
SELECT 2, 2, 20 FROM dual)
select agent_id
, mon
, commission
FROM agt_dum
MODEL PARTITION BY (agent_id)
DIMENSION BY (mon)
MEASURES (commission)
RULES ITERATE (12)
(
commission[ITERATION_NUMBER + 1] = NVL(commission[cv()], 0)
)
ORDER BY agent_id
, mon;