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?
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 |