我有以下查询：
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
