Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Ссылка на верхнюю таблицу из outer join on выражения  [new]
Tadas7
Member

Откуда: Vilnius
Сообщений: 43
Привет,

Может поможете советом, как ошибку обойти.
Вкраце ситуация такая - в запросе используется EXISTS subquery, в котором есть outer join и ссылка на поле одной из "верхних" таблиц:

SELECT a.id,
d.den
FROM a
cross join d
WHERE 
EXISTS (
    SELECT 0 FROM 
     b
    left outer join c ON (c.code = b.code AND c.den = d.den)
   WHERE a.id = b.id AND d.den> SYSDATE-5
)

или конкретнее:
SELECT a.id,
d.den
FROM (SELECT 1 id FROM dual) a
cross join (SELECT Trunc(SYSDATE)+1-LEVEL den FROM dual CONNECT BY LEVEL<=5) d
WHERE 
EXISTS (SELECT 0 FROM 
(SELECT 1 id, 2 code FROM dual) b
left outer join (SELECT Trunc(SYSDATE) den, 2 code FROM dual) c ON (c.code = b.code AND c.den = d.den)
WHERE a.id = b.id AND d.den> SYSDATE-5
)
Получаю:
ORA-00904: "D"."DEN": invalid identifier
Хотя вот так (когда то же поле верхней таблицы d.den используется не в ON, а в WHERE condition:
SELECT a.id,
d.den
FROM (SELECT 1 id FROM dual) a
cross join (SELECT Trunc(SYSDATE)+1-LEVEL den FROM dual CONNECT BY LEVEL<=5) d
WHERE 
EXISTS (SELECT 0 FROM 
(SELECT 1 id, 2 code FROM dual) b
left outer join (SELECT Trunc(SYSDATE) den, 2 code FROM dual) c ON (c.code = b.code /*AND c.den = d.den*/)
WHERE a.id = b.id AND d.den> SYSDATE-5
)
работает.

Уже который раз поднимается рука похожий запрос написать и все обламываюсь. Возможно ли переписать так, чтобы не давало ошибки? Присоединять b и c к верхним таблицам не хочется, так, как они возвращают много записей и тогда придется прокручивать их всех и потом использовать DISTINCT или GROUP BY, а меня интересует только наличие хотя бы одной записи.
26 сен 07, 13:37    [4717650]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
xymbo
Member

Откуда: Донской --> Москва
Сообщений: 2560
Вот так работает
SELECT a.id,
d.den
FROM (SELECT 1 id FROM dual) a
cross join (SELECT Trunc(SYSDATE)+1-LEVEL den FROM dual CONNECT BY LEVEL<=5) d
WHERE 
EXISTS (SELECT 0 FROM 
(SELECT 1 id, 2 code FROM dual) b, (SELECT Trunc(SYSDATE) den, 2 code FROM dual) c
WHERE a.id = b.id AND d.den> SYSDATE-5
AND c.code(+) = b.code AND c.den(+) = d.den
)
26 сен 07, 13:46    [4717751]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
Tadas7
Member

Откуда: Vilnius
Сообщений: 43
Спасибо
:)
26 сен 07, 14:05    [4717958]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
Tadas7
Member

Откуда: Vilnius
Сообщений: 43
Если позволите, скажу еще пару слов.
Все бы хорошо, если бы не одно обстоятельство. В примере я слишком упростил запрос. На деле outer join condition выражение строится на полях более, чем одной таблицы и переход на старомодный (+) синтаксис выглядет не так уж радужно.
По порядку. Хочу, чтобы работал такой запрос:
SELECT a.id,
d.den
FROM (SELECT 1 id FROM dual) a
cross join (SELECT Trunc(SYSDATE)+1-LEVEL den FROM dual CONNECT BY LEVEL<=5) d
WHERE 
EXISTS (SELECT 0 FROM 
(SELECT 1 id, 1 code FROM dual UNION ALL SELECT 1 id , 2 code FROM dual) b
cross join (SELECT LEVEL nr FROM dual CONNECT BY LEVEL<=24) e
left outer join (SELECT 2 code, Trunc(SYSDATE)+3/24 chas, 0 data FROM dual UNION ALL SELECT 2 code, Trunc(SYSDATE)+4/24 den, 1 data FROM dual ) c ON (c.code = b.code AND c.chas = d.den + (e.nr-1)/24)
WHERE a.id = b.id AND e.nr<12 AND (c.code IS NULL OR c.data = 1)  
)
Но как и уже говорил получаю ora-00904 про d.den в outer join condition.
Если перевожу в (+) синтаксис:
SELECT a.id,
d.den
FROM (SELECT 1 id FROM dual) a
cross join (SELECT Trunc(SYSDATE)+1-LEVEL den FROM dual CONNECT BY LEVEL<=5) d
WHERE 
EXISTS (SELECT 0 FROM 
(SELECT 1 id, 1 code FROM dual UNION ALL SELECT 1 id , 2 code FROM dual) b
,(SELECT LEVEL nr FROM dual CONNECT BY LEVEL<=24) e
,(SELECT 2 code, Trunc(SYSDATE)+3/24 chas, 0 data FROM dual UNION ALL SELECT 2 code, Trunc(SYSDATE)+4/24 den, 1 data FROM dual ) c 
WHERE a.id = b.id AND e.nr<12 AND (c.code IS NULL OR c.data = 1)
AND  (c.code(+) = b.code AND c.chas(+) = d.den + (e.nr-1)/24)  
)
, получаю ora-01417 потому, что использую для связки поля более, чем одной таблицы.

Работает вот так:
SELECT DISTINCT a.id,
d.den
FROM (SELECT 1 id FROM dual) a
left outer join (SELECT 1 id, 1 code FROM dual UNION ALL SELECT 1 id , 2 code FROM dual) b ON (a.id = b.id)
cross join (SELECT Trunc(SYSDATE)+1-LEVEL den FROM dual CONNECT BY LEVEL<=5) d
cross join (SELECT LEVEL nr FROM dual CONNECT BY LEVEL<=24) e
left outer join (SELECT 2 code, Trunc(SYSDATE)+3/24 chas, 0 data FROM dual UNION ALL SELECT 2 code, Trunc(SYSDATE)+4/24 den, 1 data FROM dual ) c ON (c.code = b.code AND c.chas = d.den + (e.nr-1)/24) 
WHERE e.nr<12 AND (b.id IS NULL OR c.code IS NULL OR c.data = 1) 
, но приходится прокручивать все записи.
Или вот так:
SELECT 
a.id,
d.den
FROM 
(SELECT 1 id FROM dual) a
cross join (SELECT Trunc(SYSDATE)+1-LEVEL den FROM dual CONNECT BY LEVEL<=5) d
WHERE 
EXISTS (SELECT 0 FROM 
(
SELECT b.id, b.code,
d.den,
e.nr, 
d.den + (e.nr-1)/24 chas
FROM
(SELECT 1 id, 1 code FROM dual UNION ALL SELECT 1 id , 2 code FROM dual) b
cross join (SELECT Trunc(SYSDATE)+1-LEVEL den FROM dual CONNECT BY LEVEL<=5) d
cross join (SELECT LEVEL nr FROM dual CONNECT BY LEVEL<=24) e
) f
,(SELECT 2 code, Trunc(SYSDATE)+3/24 chas, 0 data FROM dual UNION ALL SELECT 2 code, Trunc(SYSDATE)+4/24 den, 1 data FROM dual ) c
WHERE c.code (+) = f.code AND c.chas(+) = f.chas
AND f.id = a.id AND f.den = d.den 
AND f.nr<12 AND (c.code IS NULL OR c.data = 1) 
) 
, но тут дважды юзаю d.
Есть ли решение получше?
26 сен 07, 23:45    [4721185]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
Vladimir Sitnikov
Member

Откуда: Moscow, NetCracker
Сообщений: 407
А почему бы просто не обвести таблицы b и e в скобочки и обозначить за одну? Тогда и с помощью (+) джойнить можно будет.
SELECT a.id, d.den
  FROM (SELECT 1 id FROM dual) a
 cross join (SELECT Trunc(SYSDATE) + 1 - LEVEL den
               FROM dual
             CONNECT BY LEVEL <= 5) d
 WHERE EXISTS
 (SELECT 0
          FROM (select b.*, e.*
                  from (SELECT 1 id, 1 code FROM dual
                        UNION ALL
                        SELECT 1 id, 2 code FROM dual) b,
                       (SELECT LEVEL nr FROM dual CONNECT BY LEVEL <= 24) e) be,
               (SELECT 2 code, Trunc(SYSDATE) + 3 / 24 chas, 0 data FROM dual
                UNION ALL
                SELECT 2 code, Trunc(SYSDATE) + 4 / 24 den, 1 data FROM dual) c
         WHERE a.id = be.id
           and be.nr < 12
           and c.data(+) = 1
           and c.code(+) = be.code
           AND c.chas(+) = d.den + (be.nr - 1) / 24)
27 сен 07, 00:55    [4721240]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
Tadas7
Member

Откуда: Vilnius
Сообщений: 43
Действительно так работает. И довольно шустро. Спасибо за науку!
27 сен 07, 17:28    [4726243]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
Tadas7
Member

Откуда: Vilnius
Сообщений: 43
А вот так почему то не работает:
SELECT a.id, d.den
  FROM (SELECT 1 id FROM dual) a
 cross join (SELECT Trunc(SYSDATE) + 1 - LEVEL den,Trunc(SYSDATE) - LEVEL den2
               FROM dual
             CONNECT BY LEVEL <= 5) d
 WHERE EXISTS
 (SELECT 0
          FROM (select b.*, e.*, 1 sw
                  from (SELECT 1 id, 1 code FROM dual
                        UNION ALL
                        SELECT 1 id, 2 code FROM dual) b,
                       (SELECT LEVEL nr FROM dual CONNECT BY LEVEL <= 24) e) be,
               (SELECT 2 code, Trunc(SYSDATE) + 3 / 24 chas, 0 data FROM dual
                UNION ALL
                SELECT 2 code, Trunc(SYSDATE) + 4 / 24 den, 1 data FROM dual) c
         WHERE a.id = be.id
           and be.nr < 12
           and c.data(+) = 1
           and c.code(+) = be.code
           --AND c.chas(+) = d.den + (be.nr - 1) / 24)
           AND c.chas(+) = Decode(be.sw,1,d.den,d.den2) + (be.nr - 1) / 24)
1 окт 07, 12:34    [4737576]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
Vladimir Sitnikov
Member

Откуда: Moscow, NetCracker
Сообщений: 407
Tadas7
А вот так почему то не работает:
С вашими хитросплетениями джойнов только пятничные задачи задавать.

Может быть, вы саму задачу опишете? В данном случае, outer-join с самой таблицей c ничего не даёт. Не влияет таблица "C" на результат exists/not exists. Зачем она тогда вообще в запросе?
1 окт 07, 16:06    [4739491]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
Tadas7
Member

Откуда: Vilnius
Сообщений: 43
Спасибо, Владимир, за участие в решении моих хитросплетений. Действительно, мог бы дня четыре подождать до пятницы. После того, как задал вопрос, сам нашел другое решение, и потребность в таком выборочном join'е пропала. Все равно спасибо. В моем примере поле be.sw статично, но в реальной задаче оно может принимать разные значения для каждой записи be. И для меня было удивительно то, что из-за использования decode или case в выражении, Oracle решает, что такое условие неприемлемо, хотя используются поля все тех же таблиц. Потом понял, когда вместо decode выписал два условия, связанные OR'ом и в ответ получил более понятное ORA-01719: outer join operator (+) not allowed in operand of OR or IN
Вопрос снимается, остаюсь вам должен вами потраченное время.
1 окт 07, 17:54    [4740168]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
Tadas7
Member

Откуда: Vilnius
Сообщений: 43
Vladimir Sitnikov
А почему бы просто не обвести таблицы b и e в скобочки и обозначить за одну? Тогда и с помощью (+) джойнить можно будет.
SELECT a.id, d.den
  FROM (SELECT 1 id FROM dual) a
 cross join (SELECT Trunc(SYSDATE) + 1 - LEVEL den
               FROM dual
             CONNECT BY LEVEL <= 5) d
 WHERE EXISTS
 (SELECT 0
          FROM (select b.*, e.*
                  from (SELECT 1 id, 1 code FROM dual
                        UNION ALL
                        SELECT 1 id, 2 code FROM dual) b,
                       (SELECT LEVEL nr FROM dual CONNECT BY LEVEL <= 24) e) be,
               (SELECT 2 code, Trunc(SYSDATE) + 3 / 24 chas, 0 data FROM dual
                UNION ALL
                SELECT 2 code, Trunc(SYSDATE) + 4 / 24 den, 1 data FROM dual) c
         WHERE a.id = be.id
           and be.nr < 12
           and c.data(+) = 1
           and c.code(+) = be.code
           AND c.chas(+) = d.den + (be.nr - 1) / 24)


О сколько нам открытий чудных...
Хотя уже вроде и попрощался с этой темой, но она заставляет о себе вспомнить. Если выше приведенный пример прекрасно работает, то следующее уже нет:
SELECT a.id, d.den
  FROM (SELECT 1 id FROM dual) a
 cross join (SELECT Trunc(SYSDATE) + 1 - LEVEL den
               FROM dual
             CONNECT BY LEVEL <= 5) d
 WHERE EXISTS
 (SELECT 0
          FROM (select b.*, e.*
                  from (SELECT 1 id, 1 code FROM dual
                        UNION ALL
                        SELECT 1 id, 2 code FROM dual) b,
                       (SELECT LEVEL nr FROM dual CONNECT BY LEVEL <= 24) e) be,
               (SELECT 2 code, Trunc(SYSDATE) + 3 / 24 chas, 0 data FROM dual
                UNION ALL
                SELECT 2 code, Trunc(SYSDATE) + 4 / 24 den, 1 data FROM dual) c
         WHERE a.id = be.id
           and be.nr < 12
           and c.data(+) = 1
           and c.code(+) = be.code
           AND c.chas(+) = d.den + (be.nr - 1)*(Trunc(SYSDATE)-d.den) / 24)
Возвращает все ту же ORA-01417: a table may be outer joined to at most one other table.
Если с Decode и Case я еще мог это принять, то тут уже становится странно - напомню, что выражение outer join'а состоит из тех же полей тех же таблиц, но в первом случае это работает, а во втором - уже нет. Разница - в сложности выражения, хотя и в первом, и во втором случае это простые арифметические преобразования. Как считаете, может писануть SR в metalink? Хотя формально связка идет действительно с двумя таблицами - d и be. Окажется, что и впервом то случае работать не должно было...
5 окт 07, 11:59    [4759488]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116101
Tadas7
Возвращает все ту же ORA-01417: a table may be outer joined to at most one other table.
Если с Decode и Case я еще мог это принять, то тут уже становится странно - напомню, что выражение outer join'а состоит из тех же полей тех же таблиц, но в первом случае это работает, а во втором - уже нет. Разница - в сложности выражения, хотя и в первом, и во втором случае это простые арифметические преобразования. Как считаете, может писануть SR в metalink? Хотя формально связка идет действительно с двумя таблицами - d и be. Окажется, что и впервом то случае работать не должно было...


ИМХО так и могут ответить :-)
А еще посоветовать воспользоваться "продвигаемым" ANSI- Join-ом.
Там такая ошибка проявиться не должна...
5 окт 07, 12:05    [4759557]     Ответить | Цитировать Сообщить модератору
 Re: Ссылка на верхнюю таблицу из outer join on выражения  [new]
Tadas7
Member

Откуда: Vilnius
Сообщений: 43
ANSI outer join'ом я с самого начала пробовал - см. первый пост.
Или вот так:
SELECT a.id, d.den
  FROM (SELECT 1 id FROM dual) a
 cross join (SELECT Trunc(SYSDATE) + 1 - LEVEL den
               FROM dual
             CONNECT BY LEVEL <= 5) d
 WHERE EXISTS
 (SELECT 0
          FROM (select b.*, e.*
                  from (SELECT 1 id, 1 code FROM dual
                        UNION ALL
                        SELECT 1 id, 2 code FROM dual) b,
                       (SELECT LEVEL nr FROM dual CONNECT BY LEVEL <= 24) e) be
                       left outer join 
               (SELECT 2 code, Trunc(SYSDATE) + 3 / 24 chas, 0 data FROM dual
                UNION ALL
                SELECT 2 code, Trunc(SYSDATE) + 4 / 24 den, 1 data FROM dual) c ON (
           c.data = 1
           and c.code = be.code
           AND c.chas = d.den + (be.nr - 1)*(Trunc(SYSDATE)-d.den) / 24)
          WHERE a.id = be.id
           and be.nr < 12           )
на что получаю:
ORA-00904: "D"."DEN": invalid identifier
5 окт 07, 12:20    [4759696]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить