Re: connect by

Thank you very much. I have tried to get it right for a while now but so I very grateful for your help.

Sorry that I didn't notice the error in my example listing.

Regards
Roland

"Michel Cadot" <micadot_at_netcourrier.com> skrev i meddelandet news:9nil0p$2vi$1_at_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 Tue Sep 11 2001 - 01:58:11 CDT