Re: connect by

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 10 Sep 2001 17:08:08 +0200
Message-ID: <9nil0p$2vi$1@s1.read.news.oleane.net>

"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'
9 group by a.id
10 having count(*)>0
11 /

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'
9 group by a.id
10 having count(*)>0
11 /

ROOT NB_LEAVES_NOT_CLOSED

---------- --------------------
         3                    1
         7                    1

2 rows selected.

--
Have a nice day
Michel

Received on Mon Sep 10 2001 - 10:08:08 CDT