| CONNECT BY PRIOR [message #397173] |
Thu, 09 April 2009 05:23  |
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
|
Senior Member |
|
|
HI Friends,
I had created the table tree with P(parent) and C(Child) columns and my table data is
SQL> select * from tree;
C P
---------- ----------
1
2 1
3 2
4
5 4
6 5
7
8 7
9 rows selected.
And i used the query below to find the hierarchie
SQL>select level,sys_connect_by_path(C,'/') from tree start with p is null connect by prior c=p;
LEVEL SYS_CONNECT_BY_PATH(C,'/')
---------- ------------------------------
1 /1
2 /1/2
3 /1/2/3
1 /4
2 /4/5
3 /4/5/6
1 /7
2 /7/8
9 rows selected.
In the above output, i want the record which is having full path from root to leaf node for each root independent of which level it belongs to. I mean to say I want to retrieve record which is having highest level.
As in the above example,the output has to be like below
LEVEL SYS_CONNECT_BY_PATH(C,'/')
---------- ------------------------------
3 /1/2/3 --(Here for root 1 highest level is 3)
3 /4/5/6 --(Here for root 4 highest level is 3)
3 /7/8 --(Here for root 7 highest level is 2)
Thanks & Regards,
Anil MK
|
|
|
|
|
|
| Re: CONNECT BY PRIOR [message #397183 is a reply to message #397173] |
Thu, 09 April 2009 05:44  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can use Connect_By_Root to give you a grouping clause:with src as (select 1 C, to_number(null) P from dual union all
select 2, 1 from dual union all
select 3, 2 from dual union all
select 4, null from dual union all
select 5, 4 from dual union all
select 6, 5 from dual union all
select 7, null from dual union all
select 8, 7 from dual)
select max(path)
from (select sys_connect_by_path(C,'/') path
,connect_by_root(C) root
from src
start with p is null
connect by prior c=p)
group by root |
|
|
|
| Re: CONNECT BY PRIOR [message #397186 is a reply to message #397183] |
Thu, 09 April 2009 05:47 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Is the following no more valid in the guide?
| Quote: | When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.
|
Regards
Michel
|
|
|
|
| Re: CONNECT BY PRIOR [message #397192 is a reply to message #397186] |
Thu, 09 April 2009 06:10 |
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
|
Senior Member |
|
|
Hi JRowbottom,
Thanks for your quick reply.
And the query is working fine but because of below error I slightly I changed the query to accomplish the result.
ORA-01436: CONNECT BY loop in user data
with src as (SELECT C, P from tree )
select max(path)
from (select sys_connect_by_path(C,'=>') path
,connect_by_root(C) root, connect_by_iscycle
from src WHERE LEVEL >4
start with p is null
connect by NOCYCLE prior c=p)
group by root
Thanks & Regards,
Anil MK
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
