Skip to Main Content
  • Questions

  • How to find the begining of path and end of path

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Sonny.

Asked: May 27, 2020 - 5:00 pm UTC

Last updated: May 28, 2020 - 2:27 am UTC

Version: v 11.2

Viewed 1000+ times

You Asked

Hi,

I would like to know if there is anyway it return the beginning of the hierarchy and end of hierarchy for the code below.

WITH o AS 
 ( 
  SELECT 'A' as obj, 
         'C' as link 
  FROM   dual 
  UNION ALL 
  SELECT 'C', 
         'D' 
  FROM   dual 
  UNION ALL 
  SELECT 'D', 
         'E' 
  FROM   dual 
  UNION ALL 
  SELECT 'X', 
         'Y' 
  FROM   dual), 
apath AS 
 (SELECT  obj, 
                  link 
                  ,sys_connect_by_path(obj || '->' || link, ',') pth 
                  ,connect_by_iscycle as cy 
                  ,connect_by_isleaf AS lf 
  FROM   o 
  CONNECT BY nocycle obj = PRIOR link) 
SELECT * 
FROM   apath 
where lf = 1 
order by pth

I would like have the result like
Begin_Node, End_Node
A             E
C             E
D             E
X             Y


Thanks you so much in advance!

and Connor said...

This should do the trick

SQL> WITH o AS
  2   (
  3    SELECT 'A' as obj,
  4           'C' as link
  5    FROM   dual
  6    UNION ALL
  7    SELECT 'C',
  8           'D'
  9    FROM   dual
 10    UNION ALL
 11    SELECT 'D',
 12           'E'
 13    FROM   dual
 14    UNION ALL
 15    SELECT 'X',
 16           'Y'
 17    FROM   dual),
 18  apath AS
 19   (SELECT  obj,
 20                    link
 21                    ,sys_connect_by_path(obj || '->' || link, ',') pth
 22                    ,connect_by_iscycle as cy
 23                    ,connect_by_isleaf AS lf
 24    FROM   o
 25    CONNECT BY nocycle obj = PRIOR link)
 26  SELECT substr(pth,2,instr(pth,'->')-2)||
 27         ' to '||
 28         substr(pth,instr(pth,'->',-1)+2)
 29  FROM   apath
 30  where lf = 1
 31  order by pth;

SUBSTR(PTH,2,INSTR(PTH,'->')-2)||'TO'||SUBSTR(PTH,INSTR(PTH,'->',-1)+2)
------------------------------------------------------------------------------
A to E
C to E
D to E
X to Y



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library