select 5953, 'COMPANY', null from dual

union all

select 230928, 'D', 5953 from dual

union all

select 7246, 'C', 230928 from dual

union all

select 243928, 'C.5', 7246 from dual

union all

select 240961, 'C.3', 7246 from dual

union all

select 7287, 'C.4', 7246 from dual

union all

select 7286, 'C.2', 7246 from dual

union all

select 7285, 'C.1', 7246 from dual

)

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,