我正在创建一个WITH ROWID、On-Commit快速刷新实例化视图，据我所知，Oracle要求定义mview的查询使用非ANSI(旧Oracle)风格的联接.我有一个联接，我不知道如何转换为旧的Oracle联接，而且内置的Oracle转换工具不能正确处理.
将EMP视为
| EmpID | Department |
|---|---|
| 1 | Retail |
| 2 | Retail |
| 3 | Corporate |
| 4 | Corporate |
| 5 | Corporate |
和开销为
| EmpID | Overhead |
|---|---|
| 1 | $10 |
| 2 | $20 |
| 3 | $100 |
| 4 | $120 |
| 5 | $220 |
我正在try 为olapgo 规范化，所以我想做这样的事情.
| EmpID | Department | CorpOverhead |
|---|---|---|
| 1 | Retail | null |
| 2 | Retail | null |
| 3 | Corporate | $100 |
| 4 | Corporate | $120 |
| 5 | Corporate | $220 |
在ANSI中，我知道如何做到这一点，并获得我想要的结果:
select
EMP.EmpID,
EMP.Department,
OVERHEAD.Overhead as CorpOverhead
from EMP
left join OVERHEAD on
OVERHEAD.EmpID = EMP.EmpID
and EMP.Department = 'Corporate'
在古老的先知中，我被难住了.用于在连接方法之间切换的SQLDeveloper工具为我提供了以下内容
select
EMP.EmpID,
EMP.Department,
OVERHEAD.Overhead as CorpOverhead
from EMP,
OVERHEAD
where
OVERHEAD.EmpID(+) = EMP.EmpID
and EMP.Department = 'Corporate'
这导致了
| EmpID | Department | CorpOverhead |
|---|---|---|
| 3 | Corporate | $100 |
| 4 | Corporate | $120 |
| 5 | Corporate | $220 |
这不是我想要的.
我如何编写一个旧的Oracle连接来实现与ANSI连接相同的功能？