1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
SCOTT@LSC01> with structures as (
2 select 'PEF1' parent, 'XYZ' child from dual
3 union select 'XYZ','BTD3' from dual
4 union select 'XYZ','abc' from dual
5 union select 'abc','mno' from dual
6 union select 'mno','BTD26' from dual
7 union select 'BTD26','BTD54' from dual
8 union select 'PEF2','BTD3' from dual
9 union select 'PEF2','jkl' from dual
10 union select 'jkl','xxx' from dual
11 union select 'xxx','yyy' from dual
12 union select 'yyy','BTD44' from dual
13 union select 'PEF3','BTD12' from dual
14 union select 'BTD12','BTD11' from dual),
15 objects as (
16 select 'PEF1' code,'PEF' class from dual
17 union select 'PEF2','PEF' from dual
18 union select 'PEF3','PEF' from dual
19 union select 'BTD3','BTD' from dual
20 union select 'BTD26','BTD' from dual
21 union select 'BTD54','BTD' from dual
22 union select 'BTD44','BTD' from dual
23 union select 'BTD12','BTD' from dual
24 union select 'BTD11','BTD' from dual
25 union select 'abc','007' from dual
26 union select 'XYZ','007' from dual
27 union select 'xxx','007' from dual
28 union select 'yyy','007' from dual
29 union select 'jkl','007' from dual
30 union select 'mno','007' from dual)
31 select root,child, l "LEVEL" from (
32 select connect_by_root parent root, child, level l
33 from structures, objects
34 where parent=code
35 connect by parent = prior child
36 start with class='PEF'
37 ), objects
38 where code=child and class='BTD'
39 order by 1,3,2
40 /
ROOT CHILD LEVEL
----- ----- ----------
PEF1 BTD3 2
PEF1 BTD26 4
PEF1 BTD54 5
PEF2 BTD3 1
PEF2 BTD44 4
PEF3 BTD12 1
PEF3 BTD11 2 |
