| Ramesh Joyisa |
Posted: Oct 24, 2007 06:13:47 AM |
Total Post: 246
Joined: Aug, 2007
|
Can any body give a query to return hierarchical relation form the data of one table. Ex
SQL> desc test
Name Null? Type
----------------------------------------- -------- --------------
A NUMBER
B NUMBER
SQL> select * from test;
A B
---------- ----------
1 2
2 3
2 4
3 4
5 6
The output required is
1 2
2 3
2 4
3 4 |
|
|
 chinna |
| Posted: Oct 25, 2007 03:25:11 AM |  |
|
Total Post: 28
Joined: May, 2007
|
hi,
Thanks for such a good question.
Here is the Query:
SELECT LEVEL AS HierarchyLevel,NO1 ,NO2 FROM RM
CONNECT BY PRIOR NO2=NO1
START WITH NO1=1
ORDER BY LEVEL
STARTWITH Clause to specify the Start of the tree.
CONNECT BY PRIOR is the internal cursor.
LEVEL indicates the position in the hierarchal tree.
Thanks
Srawan
|
|
|
|
|
| Ramesh Joyisa |
| Posted: Oct 25, 2007 07:28:55 AM | |
|
Total Post: 246
Joined: Aug, 2007
|
Good.. Thanks.. In Addition to the above question, the next question is how get the only related number. i.e if i give parameter as 1 then i want the list of all the numbers that are related to 1.out put is
Related
------
2
3
4
|
|
|
|
|
| chinna |
| Posted: Oct 25, 2007 07:36:23 AM | |
|
Total Post: 28
Joined: May, 2007
|
hi,
It itself gives the answer if we slightly modify the query according to our requirement
SELECT DISTINCT NO2 FROM RM
CONNECT BY PRIOR NO2=NO1
START WITH NO1=2 /* The value depends on us.*/
ok
Take Care
Byee
Thanks
Srawan
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
