| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to Query Part of Hierarchical Tree List
Allen <allen_at_ms15.hinet.net> a écrit dans le message :
7ti1t7$sbe_at_netnews.hinet.net...
> Hi, All:
> Need your help:
> I created a table
> TTT (parent varchar2(100), child varchar2(100), flag varchar2(1));
> insert some data:
> parent child flag
> A B Y
> A C Y
> A D N
> B B1 N
> B B2 Y
> B B3 N
> B2 B21 N
> B2 B22 Y
> B2 B23 N
> C C1 Y
> C C2 N
> Q1: How can I get the count (flag='Y') that in every hierachical tree list
> like that (in a Single Sql Statement):
> parent child flag count
> A B Y 1 (A-B)=(Y)
> A C Y 1 (A-C)= (Y)
> A D N 0 (A-D)= (N)
> B B1 N 1 (A-B, B-B1)=(Y,N)
> B B2 Y 2 (A-B, B-B2)=(Y,Y)
> B B3 N 1 (A-B, B-B3)=(Y,N)
> B2 B21 N 2 (A-B, B-B2, B2-B21)= (Y,Y)
> B2 B22 Y 3 (A-B, B-B2, B2-B22)= (Y,Y,Y)
> B2 B23 N 2 (A-B, B-B2, B2-B23)= (Y,Y,N)
> C C1 Y 2 (A-C, C-C1) = (Y,Y)
> C C2 N 1 (A-C, C-C2) = (Y,N)
It's quite complicated because you have to know all the branch of a node when "connect by" gives onluy a local information. But we can do it.
I built an intermediate view but this is only to simplify the final query and to show the intermediate result. In that last one you can replace the name of the view by its definition.
drop view vvv;
drop view vvv2;
drop table ttt;
create table ttt (parent varchar2(10), child varchar2(10), flag varchar2(1));
insert into ttt values ('A', 'B', 'Y');
insert into ttt values ('A', 'C', 'Y');
insert into ttt values ('A', 'D', 'N');
insert into ttt values ('B', 'B1', 'N');
insert into ttt values ('B', 'B2', 'Y');
insert into ttt values ('B', 'B3', 'N');
insert into ttt values ('B2', 'B21', 'N');
insert into ttt values ('B2', 'B22', 'Y');
insert into ttt values ('B2', 'B23', 'N');
insert into ttt values ('C', 'C1', 'Y');
insert into ttt values ('C', 'C2', 'N');
commit;
create view vvv as
select rownum nb, level lvl, parent, child, flag
from ttt a
connect by prior child = parent
start with not exists ( select null from ttt b where b.child = a.parent )
/
v734> select * from vvv
2 /
NB LVL PARENT CHILD F ---------- ---------- ---------- ---------- -
1 1 A B Y
2 2 B B1 N
3 2 B B2 Y
4 3 B2 B21 N
5 3 B2 B22 Y
6 3 B2 B23 N
7 2 B B3 N
8 1 A C Y
9 2 C C1 Y
10 2 C C2 N
11 1 A D N
11 rows selected.
v734> v734> /* Count the Y flags in a branch */ v734> select substr(parent||'->'||child,1,10) branch, count 2 from ( select a.parent, b.child, 3 sum(decode(c.flag, 'Y', 1, 0)) count 4 from vvv c, vvv b, vvv a 5 where /* Select the roots */ 6 a.lvl = 1 7 and /* Search all the children of this root */ 8 ( b.nb >= a.nb 9 and b.nb < ( select nvl(min(d.nb),999999) from vvv d 10 where d.lvl = 1 and d.nb > a.nb ) ) 11 and /* Search the intermediate nodes */ 12 ( c.nb between a.nb and b.nb 13 and ( c.nb = b.nb 14 or ( c.lvl < b.lvl 15 and not exists ( select null from vvv d 16 where d.nb > c.nb and d.nb < b.nb 17 and d.lvl = c.lvl ) ) ) ) 18 group by a.parent, b.child )19 /
BRANCH COUNT
---------- ---------- A->B 1 A->B1 1 A->B2 2 A->B21 2 A->B22 3 A->B23 2 A->B3 1 A->C 1 A->C1 2 A->C2 1 A->D 0
11 rows selected.
> Q2:
> How can I get a tree list using a Single SQL to get tree listing start
> with 'A' and end with 'B22'
> like that :
> A B
> B B2
> B2 B22
>
You can either use the same intermediate view or use another one specifying the starting node:
First with the previous view:
v734> /* Display the branch between 2 nodes */
v734> select distinct c.parent, c.child
2 from vvv c, vvv b, vvv a
3 where /* Select the starting node */
4 a.parent = 'A'
5 and /* Select the ending node */
6 b.child = 'B22'
7 and /* Search the intermediate nodes */
8 ( c.nb between a.nb and b.nb 9 and ( c.nb = b.nb 10 or ( c.lvl < b.lvl 11 and not exists ( select null from vvv d 12 where d.nb > c.nb and d.nb < b.nb 13 and d.lvl = c.lvl ) ) ) )14 /
PARENT CHILD
---------- ----------
A B B B2 B2 B22
3 rows selected.
Now with a new one. In this case the final query is a little simplier.
v734> create view vvv2 as
2 select rownum nb, level lvl, parent, child, flag
3 from ttt a
4 connect by prior child = parent
5 start with a.parent = 'A'
6 /
View created.
v734>
v734> select * from vvv2
2 /
NB LVL PARENT CHILD F ---------- ---------- ---------- ---------- -
1 1 A B Y
2 2 B B1 N
3 2 B B2 Y
4 3 B2 B21 N
5 3 B2 B22 Y
6 3 B2 B23 N
7 2 B B3 N
8 1 A C Y
9 2 C C1 Y
10 2 C C2 N
11 1 A D N
11 rows selected.
v734>
v734> select distinct c.parent, c.child
2 from vvv2 c, vvv2 b
3 where /* Select the ending node */
4 b.child = 'B22'
5 and /* Search the intermediate nodes */
6 ( c.nb <= b.nb 7 and ( c.nb = b.nb 8 or ( c.lvl < b.lvl 9 and not exists ( select null from vvv d 10 where d.nb > c.nb and d.nb < b.nb 11 and d.lvl = c.lvl ) ) ) )12 /
PARENT CHILD
---------- ----------
A B B B2 B2 B22
3 rows selected.
In this example, the results of the 2 views are identical because there is only one node without parent and it is the starting node of your question.
Have a nice day
Michel Received on Mon Oct 11 1999 - 04:19:13 CDT
 |
 |