connect-by-prior: using PRIOR to drill 2-levels up

Question

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.

Awesome that you have provided a complete, tiny reproducible example, — George Joseph, Aug 04 '20 at 11:12

score 0 · Accepted Answer · answered Aug 04 '20 at 11:04

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

I wish there was a nicer way. But I think it's the only way. I actually used `regexp` functions to do the string processing bit. I find all those `INSTR` calls verbose and unintuitive. — cartbeforehorse, Sep 09 '20 at 20:01

connect-by-prior: using PRIOR to drill 2-levels up

1 Answers1