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