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!
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