sqloracle-databaseolap

Old Oracle (non-ansi) join syntax for more complex join conditions (Oracle 12c)


I am creating a WITH ROWID, on-commit fast refresh materialized view and as I understand it, Oracle requires the query that defines the mview to use non-ansi (old oracle) style joins. I have a join that I dont know how to convert to old oracle join, and that the inbuilt Oracle conversion tool doesn't handle correctly.

Consider EMP as

EmpID Department
1 Retail
2 Retail
3 Corporate
4 Corporate
5 Corporate

and OVERHEAD as

EmpID Overhead
1 $10
2 $20
3 $100
4 $120
5 $220

I am trying to denormalize for olap, so I want to do something like this

EmpID Department CorpOverhead
1 Retail null
2 Retail null
3 Corporate $100
4 Corporate $120
5 Corporate $220

In ANSI I know how to do this, and get the result I want:

select 
   EMP.EmpID, 
   EMP.Department, 
   OVERHEAD.Overhead as CorpOverhead
from EMP
left join OVERHEAD on 
   OVERHEAD.EmpID = EMP.EmpID 
   and EMP.Department = 'Corporate'

In old Oracle, I am stumped. The sqldeveloper tool to toggle between join methods gives me this

select 
   EMP.EmpID, 
   EMP.Department, 
   OVERHEAD.Overhead as CorpOverhead
from EMP,
   OVERHEAD 
where 
   OVERHEAD.EmpID(+) = EMP.EmpID 
   and EMP.Department = 'Corporate'

which results in

EmpID Department CorpOverhead
3 Corporate $100
4 Corporate $120
5 Corporate $220

which is not what I want.

How can I write an old oracle join to accomplish the same as the ANSI join?


Solution

  • You can use a CASE expression in the JOIN:

    select e.EmpID, 
           e.Department, 
           o.Overhead as CorpOverhead
    from   EMP e,
           OVERHEAD o
    where  CASE e.department WHEN 'Corporate' THEN e.EmpID END = o.EmpID (+)
    

    Which, for your the sample data, outputs:

    EMPID DEPARTMENT CORPOVERHEAD
    3 Corporate 100
    4 Corporate 120
    5 Corporate 220
    1 Retail null
    2 Retail null

    fiddle