使用外连接时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

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

解决方法

猜你在找的MsSQL相关文章