我有以下查询:

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, 1 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('04/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 assignment_id, 1 person_id, to_date('06/08/2015', 'dd/mm/yyyy') start_date, to_date('10/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 4 assignment_id, 2 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual),
       employees as (select 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 3 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', '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, 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, 1 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('04/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 assignment_id, 1 person_id, to_date('06/08/2015', 'dd/mm/yyyy') start_date, to_date('10/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 4 assignment_id, 2 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual),
       employees as (select 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 3 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', '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
------------- ---------- ---------- ---------- ----------- ------------ ----------
            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

10-06 05:40
查看更多