Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Не пойму, почему запрос не возвращает нужный результат  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Есть такой запрос:
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 все значения пустые.
Не могу понять почему.

________________________
Мы смотрим с оптимизмом...
...в оптический прицел.
15 окт 15, 18:39    [18284513]     Ответить | Цитировать Сообщить модератору
 Re: Не пойму, почему запрос не возвращает нужный результат  [new]
Elic
Member

Откуда:
Сообщений: 29991
Alibek B.
Не могу понять почему.
"_SEGMENT".SEGMENT_ID уникально. Поэтому работает только inner join на S1.
15 окт 15, 18:49    [18284542]     Ответить | Цитировать Сообщить модератору
 Re: Не пойму, почему запрос не возвращает нужный результат  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Кажется нашел.
Для 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 (столбец определен неоднозначно).
Не могу понять, что имеется ввиду.
15 окт 15, 18:50    [18284544]     Ответить | Цитировать Сообщить модератору
 Re: Не пойму, почему запрос не возвращает нужный результат  [new]
Elic
Member

Откуда:
Сообщений: 29991
Alibek B.
Но теперь не получается избавиться от дублей.
"Распаралелливание" нужно делать раньше.
15 окт 15, 18:55    [18284557]     Ответить | Цитировать Сообщить модератору
 Re: Не пойму, почему запрос не возвращает нужный результат  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Спасибо за подсказку, вроде бы разобрался.

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.
Как правильно сделать такой джойн?
15 окт 15, 19:28    [18284646]     Ответить | Цитировать Сообщить модератору
 Re: Не пойму, почему запрос не возвращает нужный результат  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Поспешил, дубли остаются:
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), то проблема решается, но запрос становится уже громоздким.
Может быть можно задачу решить как-то иначе?
15 окт 15, 19:47    [18284708]     Ответить | Цитировать Сообщить модератору
 Re: Не пойму, почему запрос не возвращает нужный результат  [new]
Elic
Member

Откуда:
Сообщений: 29991
Alibek B.
как-то иначе?
"_SEGMENT" нужно присоединять единожды.
16 окт 15, 07:22    [18285629]     Ответить | Цитировать Сообщить модератору
 Re: Не пойму, почему запрос не возвращает нужный результат  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Спасибо, теперь все получилось:
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
16 окт 15, 09:54    [18286043]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить