Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: connect by
"Roland Carlsson" <roland.c_at_swetravel.se> a écrit dans le message news: 3b9cb127_at_d2o21.telia.com...
> Hi!
> I'm making a system for support-tasks where a support-engineer shall enter a
> task about a problem and subtasks about the steps taken to solve the
> problem. This will create a tree-structure since a task can be divided into
> several sub-tasks. To list and follow the tasks i'm using a connect
> by-clause.
>
> Now the problem:
> Each subtask should be closed indepently. That means that I can't close the
> task at the root but I must close at the leafs and when searching for open
> tasks I must find those tasks that have no open subtasks. I know how I can
> eliminate branches by looking at the root but how can I eliminate them by
> looking at the child?
>
> Imagine the follwing example. parent = 0 is a root-task, status = closed
> ends a branch.
> Table tasks:
> ----------------------
> id parent status
> 1 0 new
> 2 1 closed
> 3 0 new
> 4 3 solution
> 5 4 closed
> 6 4 solution
> 7 0 new
> 8 0 new
> 9 8 solution
> 10 9 solution
> 11 10 closed
> 12 8 solution
> 13 9 solution
> 14 13 solution
> 15 14 closed
>
> If I make a query for the id of open tasks i want the following answer.
>
> id
> 3
> 7
>
> Task with id 1 and 8 all branches are closed and therefore not in the
> listing above.
>
> Thanks in advance
> Roland Carlsson
>
>
v815> create table t (id integer primary key, parent integer, status varchar2(20)); v815> insert into t values (1,0,'new'); v815> insert into t values (2,1,'closed'); v815> insert into t values (3,0,'new'); v815> insert into t values (4,3,'solution'); v815> insert into t values (5,4,'closed'); v815> insert into t values (6,4,'solution'); v815> insert into t values (7,0,'new'); v815> insert into t values (8,0,'new'); v815> insert into t values (9,8,'solution'); v815> insert into t values (10,9,'solution'); v815> insert into t values (11,10,'closed'); v815> insert into t values (12,8,'solution'); v815> insert into t values (13,9,'solution'); v815> insert into t values (14,13,'solution'); v815> insert into t values (15,14,'closed'); v815> commit; v815> select * from t; ID PARENT STATUS ---------- ---------- -------------------- 1 0 new 2 1 closed 3 0 new 4 3 solution 5 4 closed 6 4 solution 7 0 new 8 0 new 9 8 solution 10 9 solution 11 10 closed 12 8 solution 13 9 solution 14 13 solution 15 14 closed
15 rows selected.
v815> select a.id root, count(*) nb_leaves_not_closed
2 from t b, t a
3 where a.parent = 0
4 and b.id in (select id from t
5 connect by prior id = parent 6 start with id = a.id)7 and not exists (select 1 from t where parent=b.id) 8 and b.status != 'closed'
ROOT NB_LEAVES_NOT_CLOSED
---------- -------------------- 3 1 7 1 8 1
3 rows selected.
Actually branch 8 is not closed because task 12 is not closed unless there is a mistake and 13 is child of 12 and not 9:
v815> update t set parent=12 where id=13;
1 row updated.
v815> select a.id root, count(*) nb_leaves_not_closed
2 from t b, t a
3 where a.parent = 0
4 and b.id in (select id from t
5 connect by prior id = parent 6 start with id = a.id)7 and not exists (select 1 from t where parent=b.id) 8 and b.status != 'closed'
ROOT NB_LEAVES_NOT_CLOSED
---------- -------------------- 3 1 7 1
2 rows selected.
-- Have a nice day MichelReceived on Mon Sep 10 2001 - 10:08:08 CDT