I have the following connect-by-prior SQL which essentially starts at the leaf node and works its way up the tree to the parent "tree-trunc" (level-1):
with my_tree as (
select 'level 4.1' node, 'level 3.1' parent_node from dual union
select 'level 4.2' node, 'level 3.2' parent_node from dual union
select 'level 3.1' node, 'level 2' parent_node from dual union
select 'level 3.2' node, 'level 2' parent_node from dual union
select 'level 2' node, 'level 1' parent_node from dual union
select 'level 1' node, '' parent_node from dual
)
select level, t.node, t.parent_node, prior t.node child_that_pointed_me_here
from my_tree t
connect by t.node = prior t.parent_node
start with t.node like 'level 4%'
It gives me the following output:
level node parent_node child_who_pointed_me_here
----- --------- ----------- -------------------------
1 level 4.1 level 3.1
2 level 3.1 level 2 level 4.1
3 level 2 level 1 level 3.1
4 level 1 level 2
1 level 4.2 level 3.2
2 level 3.2 level 2 level 4.2
3 level 2 level 1 level 3.2
4 level 1 level 2
You can see that the instruction prior t.node (aliased as column child_who_pointed_me_here) takes me to the data in the "prior" record (i.e. the child-node from where I started), which is exactly what I want. In other words, the PRIOR keyword gives me access to data in the "previous" record.
But What I would like is to access data 2-levels (or 3 or 4 levels) previous. Something like prior prior t.node. The desired output would look as follows:
level node parent_node child_who_pointed_me_here grandchild_who_pointed_me_here
----- --------- ----------- ------------------------- ------------------------------
1 level 4.1 level 3.1
2 level 3.1 level 2 level 4.1
3 level 2 level 1 level 3.1 level 4.1
4 level 1 level 2 level 3.1
1 level 4.2 level 3.2
2 level 3.2 level 2 level 4.2
3 level 2 level 1 level 3.2 level 4.2
4 level 1 level 2 level 3.2
I've tried the obvious prior prior t.node, but it obviously just results in an unsupported-syntax type error.
The question therefore: Is there a construct in connect-by SQL which would allow me to go 2 levels back (or up) along the path?
I'm using Oracle 12c to construct this SQL, but answers in any flavour of SQL appreciated.
the sys_connect_by_path will give you the entire lineage from where you have reached the current row.
I perform string manipulation on the sys_connect_by_path by reversing and choosing the index positions of the second '/' and third '/' to exract the data in between
Using this string we can use the following to extract 2 layers up as follows.
with my_tree as (
select 'level 4.1' node, 'level 3.1' parent_node from dual union
select 'level 4.2' node, 'level 3.2' parent_node from dual union
select 'level 3.1' node, 'level 2' parent_node from dual union
select 'level 3.2' node, 'level 2' parent_node from dual union
select 'level 2' node, 'level 1' parent_node from dual union
select 'level 1' node, '' parent_node from dual
)
select level, t.node, t.parent_node, prior t.node child_that_pointed_me_here
,sys_connect_by_path(t.node,'/') as lineage
,rtrim(
reverse(
substr(
reverse(sys_connect_by_path(t.node,'/'))
,instr(reverse(sys_connect_by_path(t.node,'/')),'/',1,2)
,instr(reverse(sys_connect_by_path(t.node,'/')),'/',1,3)
-
instr(reverse(sys_connect_by_path(t.node,'/')),'/',1,2)
)
)
,'/') as two_level_up
from my_tree t
connect by t.node = prior t.parent_node
start with t.node like 'level 4%'
+-------+-----------+-------------+----------------------------+--------------------------------------+--------------+
| LEVEL | NODE | PARENT_NODE | CHILD_THAT_POINTED_ME_HERE | LINEAGE | TWO_LEVEL_UP |
+-------+-----------+-------------+----------------------------+--------------------------------------+--------------+
| 1 | level 4.1 | level 3.1 | null | /level 4.1 | |
| 2 | level 3.1 | level 2 | level 4.1 | /level 4.1/level 3.1 | |
| 3 | level 2 | level 1 | level 3.1 | /level 4.1/level 3.1/level 2 | level 4.1 |
| 4 | level 1 | | level 2 | /level 4.1/level 3.1/level 2/level 1 | level 3.1 |
| 1 | level 4.2 | level 3.2 | | /level 4.2 | |
| 2 | level 3.2 | level 2 | level 4.2 | /level 4.2/level 3.2 | |
| 3 | level 2 | level 1 | level 3.2 | /level 4.2/level 3.2/level 2 | level 4.2 |
| 4 | level 1 | | level 2 | /level 4.2/level 3.2/level 2/level 1 | level 3.2 |
+-------+-----------+-------------+----------------------------+--------------------------------------+--------------+
dbfiddle https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=0448131cc387e52eab3126dfce0a7cde