Skip to Main Content
  • Questions
  • How to find the begining of path and end of path

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Sonny.

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

Answered by: Connor McDonald - Last updated: May 28, 2020 - 2:27 am UTC

Category: SQL - Version: v 11.2

Viewed 100+ 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 we 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


More to Explore

PL/SQL

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