Не пойму, почему запрос не возвращает нужный результат

Есть такой запрос:

with
  "_SCHEDULE" as (
    select 0 as SCHEDULE_ID, '' as NAME from dual where 0=1
    union all select 1, 'schedule1' from dual
    union all select 2, 'schedule2' from dual
  ),
  "_SEGMENT" as (
    select 0 as SEGMENT_ID, 0 as SCHEDULE_ID, '' as NAME, 0 as IS_SYSTEM from dual where 0=1
    union all select 10, 1, 'segment1', 1 from dual
    union all select 20, 2, 'segment2', 1 from dual
    union all select 21, 2, 'segment2a', 0 from dual
    union all select 22, 2, 'segment2b', 0 from dual
    union all select 23, 2, 'segment2c', 0 from dual
    union all select 24, 2, 'segment2d', 0 from dual
  ),
  "_EXTENT" as (
    select 0 as EXTENT_ID, 0 as SEGMENT_ID, 0 as IS_SYSTEM, 0 as BEG_HOUR, 0 as END_HOUR from dual where 0=1
    union all select 100, 10, 1, 00, 23 from dual
    union all select 200, 20, 1, 00, 23 from dual
    union all select 211, 21, 0, 23, 00 from dual
    union all select 212, 21, 0, 00, 05 from dual
    union all select 221, 22, 0, 06, 10 from dual
    union all select 231, 23, 0, 11, 17 from dual
    union all select 242, 24, 0, 18, 22 from dual
  ),
  "_HOURS" as (select rownum-1 as HOUR from dual connect by level <= 24)
select *
from "_HOURS" H
join      "_EXTENT"   E  on (H.HOUR between E.BEG_HOUR and E.END_HOUR)
join      "_SEGMENT"  S1 on (S1.SEGMENT_ID = E.SEGMENT_ID and S1.IS_SYSTEM = 1)
left join "_SEGMENT"  S0 on (S0.SEGMENT_ID = E.SEGMENT_ID and S0.IS_SYSTEM = 0)
join      "_SCHEDULE" P  on (P.SCHEDULE_ID = nvl(S0.SCHEDULE_ID,S1.SCHEDULE_ID))
order by P.SCHEDULE_ID, H.HOUR

На мой взгляд он должен сделать следующее:
1. "Развернуть" _EXTENT из диапазона в часы.
2. Связать полученные часы с системными сегментами расписания (IS_SYSTEM=1).
3. Опционально связать полученные часы с пользовательскими сегментами расписания (IS_SYSTEM=0).
4. Связать сегменты с расписаниям, используя пользовательский или системный сегмент.
Но в этом запросе для S0 все значения пустые.
Не могу понять почему.

________________________
Мы смотрим с оптимизмом...
...в оптический прицел.

Alibek B.
Не могу понять почему.

"_SEGMENT".SEGMENT_ID уникально. Поэтому работает только inner join на S1.

Кажется нашел.
Для S1 тоже нужно было задать left join.

Но теперь не получается избавиться от дублей.
Когда для часа определены и пользовательский, и системный сегменты, нужно использовать только системный.
Такой запрос:

with
  "_SCHEDULE" as (
    select 0 as SCHEDULE_ID, '' as NAME from dual where 0=1
    union all select 1, 'schedule1' from dual
    union all select 2, 'schedule2' from dual
  ),
  "_SEGMENT" as (
    select 0 as SEGMENT_ID, 0 as SCHEDULE_ID, '' as NAME, 0 as IS_SYSTEM from dual where 0=1
    union all select 10, 1, 'segment1', 1 from dual
    union all select 20, 2, 'segment2', 1 from dual
    union all select 21, 2, 'segment2a', 0 from dual
    union all select 22, 2, 'segment2b', 0 from dual
    union all select 23, 2, 'segment2c', 0 from dual
    union all select 24, 2, 'segment2d', 0 from dual
  ),
  "_EXTENT" as (
    select 0 as EXTENT_ID, 0 as SEGMENT_ID, 0 as IS_SYSTEM, 0 as BEG_HOUR, 0 as END_HOUR from dual where 0=1
    union all select 100, 10, 1, 00, 23 from dual
    union all select 200, 20, 1, 00, 23 from dual
    union all select 211, 21, 0, 23, 00 from dual
    union all select 212, 21, 0, 00, 05 from dual
    union all select 221, 22, 0, 06, 10 from dual
    union all select 231, 23, 0, 11, 17 from dual
    union all select 242, 24, 0, 18, 22 from dual
  ),
  "_HOURS" as (select rownum-1 as HOUR from dual connect by level <= 24)
select *
from "_HOURS" H
join      "_EXTENT"   E  on (H.HOUR between E.BEG_HOUR and E.END_HOUR)
left join "_SEGMENT"  S1 on (S1.SEGMENT_ID = E.SEGMENT_ID and S1.IS_SYSTEM = 1)
left join "_SEGMENT"  S0 on (S0.SEGMENT_ID = E.SEGMENT_ID and S0.IS_SYSTEM = 0)
join      "_SEGMENT"  S  on (S.SEGMENT_ID = nvl(S0.SEGMENT_ID,S1.SEGMENT_ID))
join      "_SCHEDULE" P  on (P.SCHEDULE_ID = S.SCHEDULE_ID)
order by P.SCHEDULE_ID, H.HOUR

выдает ошибку ORA-00918 (столбец определен неоднозначно).
Не могу понять, что имеется ввиду.

Alibek B.
Но теперь не получается избавиться от дублей.

"Распаралелливание" нужно делать раньше.

Спасибо за подсказку, вроде бы разобрался.

with
  "_SCHEDULE" as (
    select 0 as SCHEDULE_ID, '' as NAME from dual where 0=1
    union all select 1, 'schedule1' from dual
    union all select 2, 'schedule2' from dual
  ),
  "_SEGMENT" as (
    select 0 as SEGMENT_ID, 0 as SCHEDULE_ID, '' as NAME, 0 as IS_SYSTEM from dual where 0=1
    union all select 10, 1, 'segment1', 1 from dual
    union all select 20, 2, 'segment2', 1 from dual
    union all select 21, 2, 'segment2a', 0 from dual
    union all select 22, 2, 'segment2b', 0 from dual
    union all select 23, 2, 'segment2c', 0 from dual
    union all select 24, 2, 'segment2d', 0 from dual
  ),
  "_EXTENT" as (
    select 0 as EXTENT_ID, 0 as SEGMENT_ID, 0 as IS_SYSTEM, 0 as BEG_HOUR, 0 as END_HOUR from dual where 0=1
    union all select 100, 10, 1, 00, 23 from dual
    union all select 200, 20, 1, 00, 23 from dual
    union all select 211, 21, 0, 23, 00 from dual
    union all select 212, 21, 0, 00, 05 from dual
    union all select 221, 22, 0, 06, 10 from dual
    union all select 231, 23, 0, 11, 17 from dual
    union all select 242, 24, 0, 19, 22 from dual
  ),
  "_HOURS" as (select rownum-1 as HOUR from dual connect by level <= 24)
select P.SCHEDULE_ID, P.NAME as SCHEDULE, H.HOUR, nvl(S0.SEGMENT_ID,S1.SEGMENT_ID) as SEGMENT_ID, nvl(S0.NAME,S1.NAME) as SEGMENT, nvl(S0.IS_SYSTEM,S1.IS_SYSTEM) as IS_SYSTEM
from "_HOURS" H
join      "_EXTENT"   E1 on (H.HOUR between E1.BEG_HOUR and E1.END_HOUR and E1.IS_SYSTEM = 1)
left join "_EXTENT"   E0 on (H.HOUR between E0.BEG_HOUR and E0.END_HOUR and E0.IS_SYSTEM = 0)
left join "_SEGMENT"  S1 on (S1.SEGMENT_ID = E1.SEGMENT_ID)
left join "_SEGMENT"  S0 on (S0.SEGMENT_ID = E0.SEGMENT_ID and S0.SCHEDULE_ID = S1.SCHEDULE_ID)
--join      "_SEGMENT"  S  on (S.SEGMENT_ID = nvl(S0.SEGMENT_ID,S1.SEGMENT_ID))
join      "_SCHEDULE" P  on (P.SCHEDULE_ID = nvl(S0.SCHEDULE_ID,S1.SCHEDULE_ID))
--where P.SCHEDULE_ID = 2 and H.HOUR = 19
order by P.SCHEDULE_ID, H.HOUR

Последний вопрос. Как только я раскомментирую джойн с альясом S, тут же снова получаю ошибку ORA-00918.
А я хотел использовать не nvl(S0.field,S1.field), а S.field.
Как правильно сделать такой джойн?

Поспешил, дубли остаются:

with
  "_SCHEDULE" as (
    select 0 as SCHEDULE_ID, '' as NAME from dual where 0=1
    union all select 1, 'schedule1' from dual
    union all select 2, 'schedule2' from dual
    union all select 3, 'schedule3' from dual
  ),
  "_SEGMENT" as (
    select 0 as SEGMENT_ID, 0 as SCHEDULE_ID, '' as NAME, 0 as IS_SYSTEM from dual where 0=1
    union all select 10, 1, 'segment1', 1 from dual
    union all select 20, 2, 'segment2', 1 from dual
    union all select 21, 2, 'segment2a', 0 from dual
    union all select 22, 2, 'segment2b', 0 from dual
    union all select 23, 2, 'segment2c', 0 from dual
    union all select 24, 2, 'segment2d', 0 from dual
    union all select 30, 3, 'segment3', 1 from dual
    union all select 31, 3, 'segment3a', 0 from dual
    union all select 32, 3, 'segment3b', 0 from dual
    union all select 33, 3, 'segment3c', 0 from dual
  ),
  "_EXTENT" as (
    select 0 as EXTENT_ID, 0 as SEGMENT_ID, 0 as IS_SYSTEM, 0 as BEG_HOUR, 0 as END_HOUR from dual where 0=1
    union all select 100, 10, 1, 00, 23 from dual
    union all select 200, 20, 1, 00, 23 from dual
    union all select 211, 21, 0, 23, 00 from dual
    union all select 212, 21, 0, 00, 05 from dual
    union all select 221, 22, 0, 06, 10 from dual
    union all select 231, 23, 0, 11, 17 from dual
    union all select 242, 24, 0, 19, 22 from dual
    union all select 300, 30, 1, 00, 23 from dual
    union all select 310, 31, 0, 00, 04 from dual
    union all select 320, 32, 0, 06, 10 from dual
    union all select 330, 33, 0, 12, 20 from dual
  ),
  "_HOURS" as (select rownum-1 as HOUR from dual connect by level <= 24)
select * --P.SCHEDULE_ID, P.NAME as SCHEDULE, H.HOUR, nvl(S0.SEGMENT_ID,S1.SEGMENT_ID) as SEGMENT_ID, nvl(S0.NAME,S1.NAME) as SEGMENT, nvl(S0.IS_SYSTEM,S1.IS_SYSTEM) as IS_SYSTEM
from      "_HOURS"    H
join      "_EXTENT"   E1 on (H.HOUR between E1.BEG_HOUR and E1.END_HOUR and E1.IS_SYSTEM = 1)
left join "_EXTENT"   E0 on (H.HOUR between E0.BEG_HOUR and E0.END_HOUR and E0.IS_SYSTEM = 0)
left join "_SEGMENT"  S1 on (S1.SEGMENT_ID = E1.SEGMENT_ID)
left join "_SEGMENT"  S0 on (S0.SEGMENT_ID = E0.SEGMENT_ID and S0.SCHEDULE_ID = S1.SCHEDULE_ID)
join      "_SCHEDULE" P  on (P.SCHEDULE_ID = nvl(S0.SCHEDULE_ID,S1.SCHEDULE_ID))
--where P.SCHEDULE_ID = 2 and H.HOUR = 19
order by P.SCHEDULE_ID, H.HOUR

Если в альясах E1 и E0 вместо таблицы использовать подзапрос (чтобы получить связанный SCHEDULE_ID), то проблема решается, но запрос становится уже громоздким.
Может быть можно задачу решить как-то иначе?

Alibek B.
как-то иначе?

"_SEGMENT" нужно присоединять единожды.

Спасибо, теперь все получилось:

select P.SCHEDULE_ID, P.NAME as SCHEDULE, H.HOUR, S.SEGMENT_ID, S.NAME as SEGMENT, S.IS_SYSTEM
from      "_HOURS"    H
join      (
          select E.EXTENT_ID, E.SEGMENT_ID, S.SCHEDULE_ID, E.BEG_HOUR, E.END_HOUR
          from "_EXTENT" E join "_SEGMENT" S on (S.SEGMENT_ID = E.SEGMENT_ID)
          where E.IS_SYSTEM = 1
          )           E1 on (H.HOUR between E1.BEG_HOUR and E1.END_HOUR)
left join (
          select E.EXTENT_ID, E.SEGMENT_ID, S.SCHEDULE_ID, E.BEG_HOUR, E.END_HOUR
          from "_EXTENT" E join "_SEGMENT" S on (S.SEGMENT_ID = E.SEGMENT_ID)
          where E.IS_SYSTEM = 0
          )           E0 on (H.HOUR between E0.BEG_HOUR and E0.END_HOUR and E0.SCHEDULE_ID = E1.SCHEDULE_ID)
join      "_SEGMENT"  S  on (S.SEGMENT_ID = nvl(E0.SEGMENT_ID,E1.SEGMENT_ID))
join      "_SCHEDULE" P  on (P.SCHEDULE_ID = S.SCHEDULE_ID)
order by P.SCHEDULE_ID, H.HOUR