使用外连接时where子句中的’OR’的SQL替代方法

select * from
from assignments dah,employees emp 
where 
    dah.person_id=emp.person_id(+)
and 
(dah.effective_end_date between emp.date_from(+) and emp.date_to(+)
and dah.effective_end_date between emp.valid_from(+) and emp.valid_to(+))
or   
(dah.effective_start_date between emp.date_from(+) and emp.date_to(+)
and dah.effective_start_date between emp.valid_from(+) and emp.valid_to(+))

我得到以下消息：’外部连接运算符()不允许在OR或IN’的操作数中.我知道使用2个带有内连接的联合是一个解决方案,但我不能使用它因为我实际上有很多代码(我提供的代码只是一个例子).

编辑：我需要通过oracle语法完成此操作,因为我使用数据仓库,我们的ETL不完全支持显式语法.也许有一些我没有看到的东西,这可以用不同的方式写出来？

编辑nr.2：可能以某种方式实现日期重叠逻辑而不使用OR和oracle语法？

解决方法

with assignments as (select 1 assignment_id,1 person_id,to_date('01/08/2015','dd/mm/yyyy') start_date,to_date('03/08/2015','dd/mm/yyyy') end_date from dual union all
                     select 2 assignment_id,to_date('02/08/2015',to_date('04/08/2015','dd/mm/yyyy') end_date from dual union all
                     select 3 assignment_id,to_date('06/08/2015',to_date('10/08/2015','dd/mm/yyyy') end_date from dual union all
                     select 4 assignment_id,2 person_id,'dd/mm/yyyy') end_date from dual),employees as (select 1 person_id,'dd/mm/yyyy') end_date from dual union all
                     select 3 person_id,'dd/mm/yyyy') end_date from dual)
select *
from   assignments dah,employees emp
where  dah.person_id = emp.person_id (+)
and    dah.start_date <= emp.end_date (+)
and    dah.end_date >= emp.start_date (+);

ASSIGNMENT_ID  PERSON_ID START_DATE END_DATE   PERSON_ID_1 START_DATE_1 END_DATE_1
------------- ---------- ---------- ---------- ----------- ------------ ----------
            2          1 02/08/2015 04/08/2015           1 01/08/2015   03/08/2015
            1          1 01/08/2015 03/08/2015           1 01/08/2015   03/08/2015
            3          1 06/08/2015 10/08/2015                                    
            4          2 02/08/2015 03/08/2015

你确定你的外部连接是正确的吗？你确定你实际上不是在追求以下事项吗？：

with assignments as (select 1 assignment_id,employees emp
where  dah.person_id (+) = emp.person_id
and    dah.start_date (+) <= emp.end_date
and    dah.end_date (+) >= emp.start_date;

ASSIGNMENT_ID  PERSON_ID START_DATE END_DATE   PERSON_ID_1 START_DATE_1 END_DATE_1
------------- ---------- ---------- ---------- ----------- ------------ ----------
            1          1 01/08/2015 03/08/2015           1 01/08/2015   03/08/2015
            2          1 02/08/2015 04/08/2015           1 01/08/2015   03/08/2015
                                                         3 01/08/2015   03/08/2015

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!