By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE persons(id, code, parent_id) AS
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;
8 rows affected
WITH rsqfc ( children_id, children_code, parent_id, parent_code, grandparent_id, grandparent_code ) AS (
SELECT id,
code,
NULL,
NULL,
NULL,
NULL
FROM persons
WHERE parent_id IS NULL
UNION ALL
SELECT p.id,
p.code,
r.children_id,
r.children_code,
r.parent_id,
r.parent_code
FROM rsqfc r
INNER JOIN persons p
ON ( r.children_id = p.parent_id )
)
SELECT *
FROM rsqfc
| CHILDREN_ID | CHILDREN_CODE | PARENT_ID | PARENT_CODE | GRANDPARENT_ID | GRANDPARENT_CODE |
|---|---|---|---|---|---|
| 5953 | COMPANY | null | null | null | null |
| 230928 | D | 5953 | COMPANY | null | null |
| 7246 | C | 230928 | D | 5953 | COMPANY |
| 243928 | C.5 | 7246 | C | 230928 | D |
| 240961 | C.3 | 7246 | C | 230928 | D |
| 7287 | C.4 | 7246 | C | 230928 | D |
| 7286 | C.2 | 7246 | C | 230928 | D |
| 7285 | C.1 | 7246 | C | 230928 | D |