| Interrupt a tree search [message #6553] |
Thu, 24 April 2003 16:44  |
Per W
Messages: 4
Registered: April 2003
|
Junior Member |
|
|
Hi,
I have a tree table. Starting from a given point in the tree I need to descend down towards the leafs. CONNECT BY PRIOR is useful for this.
FOR obj_cursor IN (SELECT id, type FROM tree_table
START WITH id=v_id CONNECT BY PRIOR id=parent_id)
LOOP
...
END LOOP;
Lets assume that the value of type can be 'green' or 'red'. As long as type is 'green' I want to proceed, but as soon as I hit a node which is 'red' I want to interrupt all further processing in this branch of the tree, i.e. ignore all the children of the 'red' node. The reason to do this is that I believe there are some performance gains in doing it.
Please, show me some ideas how to do this.
Thanks for your time.
Per.
|
|
|
|
|
|
|
|
| Re: Interrupt a tree search [message #6556 is a reply to message #6555] |
Thu, 24 April 2003 17:35  |
Per W
Messages: 4
Registered: April 2003
|
Junior Member |
|
|
I'm sorry, but I oversimplified my example. The decision to stop process a specific branch is not directly related to the 'type' value. Instead 'type' is actually a name of a table from which I have to fetch the row matching the 'id' value. In this row there is a column that has the value 'red' or 'green'.
(So I need to use some dynamic sql inside the loop)
Now I have probably lost many readers, but maybe someone is still around...
|
|
|
|
| Re: Interrupt a tree search [message #6557 is a reply to message #6556] |
Thu, 24 April 2003 17:55 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I did this quick example before I saw your latest reply, but the same concept applies. You could create a function that accepts a table name. In that function, do the NDS lookup and return a value as appropriate. In the expanded CONNECT BY clause (see second example below), instead of directly evaluating the value of TYPE_, call the function and evaluate the return value:
sql>select id, parent_id, type_
2 from t
3 start with id = 1
4 connect by prior id = parent_id;
ID PARENT_ID TYPE_
--------- --------- ----------
1 GREEN
2 1 GREEN
20 2 GREEN
3 1 RED
30 3 RED
4 1 GREEN
40 4 GREEN
41 4 GREEN
42 4 RED
43 42 GREEN
10 rows selected.
sql>select id, parent_id, type_
2 from t
3 start with id = 1
4 connect by prior id = parent_id <b>and type_ <> 'RED'</b>;
ID PARENT_ID TYPE_
--------- --------- ----------
1 GREEN
2 1 GREEN
20 2 GREEN
4 1 GREEN
40 4 GREEN
41 4 GREEN
6 rows selected. |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
