Home » SQL & PL/SQL » SQL & PL/SQL » CONNECT BY PRIOR (Oracle 10g, Release2 , Windows XP)
CONNECT BY PRIOR [message #397173] Thu, 09 April 2009 05:23 Go to next message
anil_mk
Messages: 137
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 #397181 is a reply to message #397173] Thu, 09 April 2009 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the one that has the max value (path) for a given root.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: CONNECT BY PRIOR [message #397183 is a reply to message #397173] Thu, 09 April 2009 05:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
anil_mk
Messages: 137
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
Previous Topic: dbms_xmldom.writetofile(DOMDocument,path)
Next Topic: Regular Express for searching Name
Goto Forum:
  


Current Time: Sun Dec 11 08:33:41 CST 2016

Total time taken to generate the page: 0.13821 seconds