Помогите с connect by prior...

 
0
 
Oracle
ava
Vas | 09.03.2006, 10:00
Добрый день. Подскажите, пожалуйста, как можно использовать в одном запросе JOIN's и CONNECT BY PRIOR?

SELECT an.num_device, ad.id, ad.id_parent, ad.id_device, ad.name
FROM all_devices ad, ats_number an
WHERE ad.id_device=an.id(+)

Если сюда добавить connect by, то запрос не выполняется, пробовал его делать вложенным результат "0" :(
Comments (8)
ava
Vas | 10.03.2006, 10:50 #
Мужики ткните носом куда копать. Пробовал view создавать из запроса та же ошибка

ORA-01437: cannot have join with CONNECT BY

added later:
Во что нашел :(

A join operation was specified with a CONNECT BY clause. If a CONNECT BY clause is used in a SELECT
statement for a tree-structured query, only one table may be referenced in the query.
ava
LSD | 10.03.2006, 11:05 #
Ну там же написано, что только одна таблица может присутсвовать в запросе с CONNECT BY, а в JOIN запросе их как минимум две.

Если очень надо, то попробуй создать вьюер на JOIN, а по нему уже делать CONNECT BY.
ava
Vas | 10.03.2006, 11:31 #
Цитата (LSD @ 10.3.2006, 11:05 findReferencedText)
Если очень надо, то попробуй создать вьюер на JOIN, а по нему уже делать CONNECT BY

Пробовал создавать вьювер из приведенного мной запроса, а потом делать из него запрос с CONNECT BY эффект тот же. Придется наверное использовать временную таблицу или может еще как-нибудь можно ?
ava
LSD | 10.03.2006, 13:52 #
Цитата (Vas @ 10.3.2006, 11:31 findReferencedText)
Придется наверное использовать временную таблицу или может еще как-нибудь можно ?

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

P.S. Можешь выложить скрипты по созданию табличек, и описание запроса, который ты пытаешься выполнить.
ava
Vas | 12.03.2006, 10:58 #
Цитата (LSD @ 10.3.2006, 13:52 findReferencedText)
Можешь выложить скрипты по созданию табличек, и описание запроса, который ты пытаешься выполнить.

Описание запроса следущее, нужно выбрать в виде ветки все устройства из таблицы all_devices причем и показать номер устройства из таблицы num_devices. Таблы связаны внешним ключом.
Вот таблицы:

create table num_devices
(id number primary key,
num_device varchar2(10))
/
insert into num_devices values(1, '11111')
/
insert into num_devices values(2, '22222')
/
insert into num_devices values(3, '33333')
/
insert into num_devices values(4, '44444')
/
insert into num_devices values(5, '55555')
/
insert into num_devices values(6, '66666')
/
insert into num_devices values(7, '77777')
commit
/
create table all_devices
(id number primary key,
id_parent number,
foreign key (id_parent) references all_devices on delete set null,
id_device number,
foreign key (id_device) references num_devices,
name varchar2(50))
/
insert into all_devices values(1, null, 1, 'Главная')
/
insert into all_devices values(2, 1, 2, 'Подчиненная 1')
/
insert into all_devices values(3, 2, 3, 'Подчиненная 2')
/
insert into all_devices values(4, 1, 4, 'Подчиненная 3')
/
insert into all_devices values(5, null, 5, 'Главная')
/
insert into all_devices values(6, 5, 6, 'Подчиненная')
/
insert into all_devices values(7, 5, 1, 'Подчиненная 2')
/
commit
/

А вот запрос:

SELECT an.num_device, ad.id, ad.id_parent, ad.id_device, ad.name
FROM all_devices ad, ats_number an
WHERE ad.id_device=an.id(+)
ava
LSD | 12.03.2006, 23:32 #
У меня все работает как с view, так и с подзапросом. Запрос
select id, id_parent, num_device, id_device, LPAD('    ',2*(LEVEL - 1)) || name name
from
(
SELECT nd.num_device, ad.id, ad.id_parent, ad.id_device, ad.name
FROM all_devices ad, num_devices nd
WHERE ad.id_device=nd.id(+)
)
start with id_parent is null
connect by prior id = id_parent

возвращает:
 ID  ID_PARENT NUM_DEVICE  ID_DEVICE NAME              
--- ---------- ---------- ---------- ------------------
1 11111 1 Главная
2 1 22222 2 Подчиненная 1
3 2 33333 3 Подчиненная 2
4 1 44444 4 Подчиненная 3
5 55555 5 Главная
7 5 11111 1 Подчиненная 2
6 5 66666 6 Подчиненная


P.S. Я проверял на 10g.
ava
Vas | 13.03.2006, 07:56 #
Цитата (LSD @ 12.3.2006, 23:32 findReferencedText)
P.S. Я проверял на 10g.

А на 8.1.7 не хотит :(

SQL> select id, id_parent, num_device, id_device, LPAD(' ',2*(LEVEL - 1)) || name name
2 from
3 (
4 SELECT nd.num_device, ad.id, ad.id_parent, ad.id_device, ad.name
5 FROM all_devices ad, num_devices nd
6 WHERE ad.id_device=nd.id(+)
7 )
8 start with id_parent is null
9 connect by prior id = id_parent
10 /
FROM all_devices ad, num_devices nd
*
ERROR at line 5:
ORA-01437: cannot have join with CONNECT BY
ava
LSD | 14.03.2006, 00:09 #
Сегодня проверил на 9-ке, тоже работает. На восьмерке смогу глянуть, только через пару дней.
Please register or login to write.
Firm of day
Вы также можете добавить свою фирму в каталог IT-фирм, и публиковать статьи, новости, вакансии и другую информацию от имени фирмы.
Подробнее
Contributors
ava  LSD ava  Vas
advanced
Submit