connect by

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 Received on Mon Sep 10 2001 - 07:25:32 CDT