Hi,
I am facing a similar problem but with MS SQL Server. Do you know if
there is a similar query we can use in SQL Server to handle these
problems.
I will appreciate your help
regards
Hassan
In article <7tsa4o$n3t$1_at_oceanite.cybercable.fr>,
"Michel Cadot" <micadot_at_netcourrier.com> wrote:
>
>
> 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
>
>
--
Sultan Hassan Noori
Tabaq Information Systems
email: hassan_at_tabaq.com
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Oct 18 1999 - 06:09:58 CDT
