Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

complex connect by prior query

337364Dec 17 2002 — edited Dec 19 2002
I need SQL(for hierarchical tree) for a function which accepts node as input parameter and returns ref cursor.

Following is a sample tree:

1
--2.1
----3.1
------4.1
--------5.1
----------6.1
----------6.2
----3.2
------4.2
--------5.2
--2.2
----3.2
------4.2
--------5.2
----3.3
----3.4
------4.1
--------5.1
----------6.1
----------6.2

1 is at the root level and 2.1 & 2.2 are immediate children and so on.
The output tree should be all related parents and children of the passed node.

e.g:

If the input is 4.1, the output tree will be:
1
--2.1
----3.1
------4.1
--------5.1
----------6.1
----------6.2
--2.2
----3.4
------4.1
--------5.1
----------6.1
----------6.2

If the input is 4.2, the output tree will be:
1
--2.1
----3.2
------4.2
--------5.2
--2.2
----3.2
------4.2
--------5.2

The complex part, I guess, is to remove unwanted(not related) branches from the tree.

Following is the representation of the table RELATIONSHIP

ID PARENT CHILD
1-------1-------2.1
2-------1-------2.2
3-------2.1-----3.1
4-------2.1-----3.2
5-------2.2-----3.2
6-------2.2-----3.3
7-------2.2-----3.4
8-------3.1-----4.1
9-------3.2-----4.2
10------3.4-----4.1
11------4.1-----5.1
12------4.2-----5.2
13------5.1-----6.1
14------5.1-----6.2

Pls. help me out to form this CONNECT BY PRIOR query.

Thanks in advance.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 16 2003
Added on Dec 17 2002
4 comments
209 views