Overwriting nulls with parent data in a hierarchical query

Question

I have a table with the following format:

pk    fk     parent
===== ====== ========
001   23     000
002   null   001
003   46     001
004   12     000
005   null   004
006   null   005
=====================

pk is the primary key for each row, fk is a foreign key to another table I'll need to join later, and parent is the record's hierarchical parent. I can create a hierarchical query on this data with

select 
    lpad(' ',2*level)||pk "primary_key",
    fk "foreign_key",
    sys_connect_by_path(pk,'/') "path"
from example_table
connect by prior pk = parent
;

My question is how do I overwrite a row's null foreign key with that of the lowest ancestor without a null? My intended output in this scenario is

pk        fk   parent
========= ==== ======
001       23   000
  002     23   001
  003     46   001
004       12   000
  005     12   004
    006   12   005
====================

(primary keys padded to show hierarchical structure.)

Answer 1

You could express this with a standard recursive common table expression:

with cte (pk, fk, parent, lvl, pat) as (
    select to_char(pk), fk, parent, 0, to_char(pk) from mytable where parent = 0
    union all
    select lpad(' ', 2 * (lvl + 1)) || t.pk, coalesce(t.fk, c.fk), t.parent, c.lvl + 1, c.pat || '/' || t.pk
    from cte c
    inner join mytable t on t.parent = c.pk
)
select pk, fk, parent from cte order by pat

Demo on DB Fiddlde:

PK    | FK | PARENT
:---- | -: | -----:
1     | 23 |      0
  2   | 23 |      1
  3   | 46 |      1
4     | 12 |      0
  5   | 12 |      4
    6 | 12 |      5

Answer 2

Just use sys_connect_by_path(fk,'/') to aggregate parent FKs and take last one using regexp:

with example_table( pk, fk, parent) as (
select '001', 23  , '000' from dual union all
select '002', null, '001' from dual union all
select '003', 46  , '001' from dual union all
select '004', 12  , '000' from dual union all
select '005', null, '004' from dual union all
select '006', null, '005' from dual 
)
select 
    lpad(' ',2*level)||pk "primary_key",
    fk "foreign_key",
    nvl(fk, regexp_substr(sys_connect_by_path(fk,'/'),'(\d+)/*$',1,1,'',1)) fk2,
    sys_connect_by_path(pk,'/') "path"
from example_table
connect by prior pk = parent
;

Results:

primary_key  foreign_key        FK2 path
------------ ----------- ---------- --------------------------------------------------------------------------------
  001                 23         23 /001
    002                          23 /001/002
    003               46         46 /001/003
  004                 12         12 /004
    005                          12 /004/005
      006                        12 /004/005/006
  002                               /002
  003                 46         46 /003
  005                               /005
    006                             /005/006
  006                               /006

11 rows selected.