• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: OUTER JOIN

Partitioned Outer Join

16 Tuesday Jun 2015

Posted by troroblog in Tipps and Tricks

≈ Leave a comment

Tags

ORACLE, OUTER JOIN, SQL

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;

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...