Home » SQL & PL/SQL » SQL & PL/SQL » SYS_CONNECT_BY_PATH (oracle 10g)
SYS_CONNECT_BY_PATH [message #409740] Tue, 23 June 2009 15:57 Go to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hello Gurus,

I have a question on SYS_CONNECT_BY_PATH.

I am having the below query:

select SYS_CONNECT_BY_PATH(name, '\') path from dtree connect by prior dataid = parentid start with dataid = 2195674

and this will result me like:

folder A\folder B\Folder C\Folder D
folder A\folder B\Folder C\Folder E
folder A\folder B\Folder C\
folder A\folder B\
folder A\

and I am filtering it by level (level=4) and will result will be
folder A\folder B\Folder C\Folder D

I now thinking is there any inbuilt functions to split the path into separte columns like
folder A | folder B | Folder C | Folder D
.....
...

Or I will have to use the substr & instr functions..

Please throw some light..

Thanks,
Mahesh
Re: SYS_CONNECT_BY_PATH [message #409746 is a reply to message #409740] Tue, 23 June 2009 19:02 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Why not just do it with SYS_CONNECT_BY_PATH(name, ' | ')
Re: SYS_CONNECT_BY_PATH [message #409757 is a reply to message #409746] Tue, 23 June 2009 21:00 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Sorry, I hope you didn't get me.

I mean the path into a separate columns:
for example:
If we use the SYS_CONNECT_BY_PATH(name,'\') we will get the result as folder A\folder B\folder C\Folder D in one column. What I am looking is a way to separate this single column into multiple columns like folder A in one column, folder b in other column, folder C & folder D in other columns respectively..

I hope you get that now.

Thanks,
Mahesh
Re: SYS_CONNECT_BY_PATH [message #409772 is a reply to message #409757] Tue, 23 June 2009 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I mean the path into a separate columns

This is one reason for you to read OraFAQ Forum Guide, especially "How to format your post?" section, use code tags and align the columns in result.

Use SUBSTR and INSTR to get it into columns.

Regards
Michel
Re: SYS_CONNECT_BY_PATH [message #409774 is a reply to message #409757] Tue, 23 June 2009 23:35 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
No ready-made function available.
Go as Michel sir suggested.

regards,
Delna
Re: SYS_CONNECT_BY_PATH [message #409896 is a reply to message #409774] Wed, 24 June 2009 06:58 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hi Michel,

I am aware of the SUBSTR & INSTR functions but I am wondering if there is any other logic other than this because the SQL Query will be very messy if I have used SUBSTR, INSTR functions for 4 columns..

Thanks a lot.

Mahesh
Re: SYS_CONNECT_BY_PATH [message #409909 is a reply to message #409896] Wed, 24 June 2009 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not messy if you write it cleanly.
Anyway, there is no other (kind of) way (you can use regex_substr but it is not much clearer).

Regards
Michel
Re: SYS_CONNECT_BY_PATH [message #618488 is a reply to message #409909] Sat, 12 July 2014 07:44 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Can somebody please post an example of how substr/instr can be used to achieve this.

Thanks
Re: SYS_CONNECT_BY_PATH [message #618489 is a reply to message #409740] Sat, 12 July 2014 08:03 Go to previous message
Solomon Yakobson
Messages: 2504
Registered: January 2010
Location: Connecticut, USA
Senior Member
with t as (
           select  connect_by_root ename l1_ename,
                   lag(ename,2) over(order by 1) l2_ename,
                   prior ename l3_ename,
                   ename l4_ename,
                   level l
             from  emp
             start with mgr is null
             connect by mgr = prior empno
          )
select  l1_ename,
        l2_ename,
        l3_ename,
        l4_ename
  from  t
  where l = 4
/

L1_ENAME   L2_ENAME   L3_ENAME   L4_ENAME
---------- ---------- ---------- ----------
KING       JONES      SCOTT      ADAMS
KING       ADAMS      FORD       SMITH

SQL>


SY.
Previous Topic: CBO
Next Topic: Order of WHERE columns doesn't matter? The optimizer will handle that?
Goto Forum:
  


Current Time: Thu Dec 08 16:41:19 CST 2016

Total time taken to generate the page: 0.09524 seconds