|
-
 Equivalent of Hierarchy SQL in Oracle
Hi All,
We have some performance issues with 'CONNECT BY PRIOR' SQL statement and trying to find an alternate SQL which gives the same output.
Example:
ENum-----------------Mgr
Julie
Andrew---------------Julie
Mark-----------------Andrew
Matt-----------------Andrew
Wyatt----------------Julie
Jenny----------------Wyatt
SELECT enum, mgr FROM START WITH enum IS NULL
CONNECT BY PRIOR enum = mgr;
Output:
-------
Julie
---Andrew
------Mark
------Matt
---Wyatt
------Jenny
---Joel
How do I get the same output without using CONNECT BY PRIOR command. I dont mind creating a new table that will keep all the possible combinations between enum and mgr columns.
Please provide your solution. I really appreciate it.
Thanks in advance,
Rao
-
Do you have to have the rpad -- notation, or is the representation of parent-child-sibling sufficient?
-
drop table org_chart;
drop table names;
-- 1Julie14
-- 2andrew7 8wyatt11 12joel13
-- 3mark4 5matt6 9jenny10
create table org_chart
(id number, left number, right number);
insert into org_chart values
(1, 1, 14);
insert into org_chart values
(2, 2, 7);
insert into org_chart values
(5, 3, 4);
insert into org_chart values
(6, 5, 6);
insert into org_chart values
(3, 8, 11);
insert into org_chart values
(7, 9, 10);
insert into org_chart values
(4, 12, 13);
create table names
(id number,
name varchar2(10));
insert into names values
(1, 'Julie');
insert into names values
(2, 'Andrew');
insert into names values
(5, 'Mark');
insert into names values
(6, 'Matt');
insert into names values
(3, 'Wyatt');
insert into names values
(7, 'Jenny');
insert into names values
(4, 'Joel');
create or replace view org_view (id, level_0, level_1)
as
select a.id,
case when count(c.id) = 2
then b.id
else null end as level_0,
case when count(c.id) = 3
then b.id
else null end as level_1
from org_chart a,
org_chart b,
org_chart c
where
a.left between b.left and b.right
and
c.left between b.left and b.right
and
a.left between c.left and c.right
group by a.id, b.id;
col level_0 format a8
col level_1 like level_0
select a.id, b.name,
decode(max(level_0),1,'Julie',2,'Andrew',3,'Wyatt') "level_0",
decode(max(level_1),1,'Julie') "level_1"
from org_view a, names b
where a.id=b.id
group by a.id, b.name
order by 1;
ID NAME level_0 level_1
---------- ---------- -------- --------
1 Julie
2 Andrew Julie
3 Wyatt Julie
4 Joel Julie
5 Mark Andrew Julie
6 Matt Andrew Julie
7 Jenny Wyatt Julie
-
How about tackling the preformance problem with the CONNECT BY query?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Click Here to Expand Forum to Full Width
|
|
Senior Advisor
Reply With Quote