Get GrandParent and Parent info from the children id with level in Oracle

Question

I would like to retrieve the information (ID and CODE) of the Grand Parent (TOP Level) and the Parent from the Children Id in a query thanks to the ORACLE feature "CONNECT BY". The best could be to retrieve the complete historical data (all ancestors and children in a same query).

Here the data:

ID     |  CODE     |  PARENT_ID
5953   |  COMPANY  |  
230928 |  D        |  5953   
7246   |  C        |  230928 
243928 |  C.5      |  7246   
240961 |  C.3      |  7246   
7287   |  C.4      |  7246   
7286   |  C.2      |  7246   
7285   |  C.1      |  7246   

Here the results that I want:

CHILDREN_ID | CHILDREN_CODE | PARENT_ID | PARENT_CODE | GRANDPARENT_CODE
5953        |  COMPANY      |           |             |  
230928      |  D            |  5953     |  COMPANY    |  
7246        |  C            |  230928   |  D          |  COMPANY
243928      |  C.5          |  7246     |  C          |  D
240961      |  C.3          |  7246     |  C          |  D
7287        |  C.4          |  7246     |  C          |  D
7286        |  C.2          |  7246     |  C          |  D
7285        |  C.1          |  7246     |  C          |  D

I created this query:

SELECT ID AS "CHILDREN_ID", CODE AS "CHILDREN_CODE", PARENT_ID , PARENT_CODE,  CONNECT_BY_ROOT CODE "GRANT_PARENT_CODE"
FROM PERSONS
WHERE LEVEL > 1
CONNECT BY PRIOR ID = PARENT_ID

But I don't retrieve the correct information for the Grand parent.

Could you please help me with that ?

Answer 1

I think I made it! Query itself looks scary, but I'll explain:

select p.id children_id, 
       p.code children_code, 
       p.parent_id, 
       prior p.code parent_code,
       case when level = 2 then null
            when level = 3 then
              connect_by_root code
            else 
              substr(sys_connect_by_path(code, '\'), 
                     instr(sys_connect_by_path(code, '\'), '\', 1, 2)+1, 
                     (instr(sys_connect_by_path(code, '\'), '\', 1, 3)) - (instr(sys_connect_by_path(code, '\'), '\', 1, 2)+1)) 
       end grandparent_code,
       case when level = 2 then null
            when level = 3 then connect_by_root to_char(id)
            else substr(sys_connect_by_path(id, '\'), 
                 instr(sys_connect_by_path(id, '\'), '\', 1, 2)+1, 
                 (instr(sys_connect_by_path(id, '\'), '\', 1, 3)) - (instr(sys_connect_by_path(id, '\'), '\', 1, 2)+1)) 
   end grandparent_id
  from persons p
  connect by prior p.id = p.parent_id
  start with p.parent_id is null;

I did use connect_by_path in order to display a path from root towards to child entry. See the doc for details.

It will provide path like '..\grand-grand-parent\grand-parent\parent\child' so just subst out grand parent is left. Exceptions are cases when level = 2 (no grand-parent) and level3 when it is enough to display just a root.

I hope this will help you. Anyway +1 from me for an interesting question

dbfiddle