Oracle Sql Recursion To Find First Instance Of Non-null Column Value

I'll try and explain how the table is laid out so that what I need might be a bit more clear. ############################################################### # cid # iid # child ci

Solution 1:

I dont know what exactly do you need, but you could start with tihs statment

select cid, iid, level, connect_by_root(target_cid), connect_by_root(target_iid)
from tab
connect by    prior cid = child_cid
          AND prior iid = child_iid
          AND target_cid isnull          
; 

and then filtern the entries you need

select *
from 
(
select cid, iid, level, connect_by_root(target_cid) as target_cid, connect_by_root(target_iid) as target_iid
from tab
connect by    prior cid = child_cid
          AND prior iid = child_iid
          AND target_cid isnull          
)
where target_cid isnotnull
;          

    CID IID TARGET_CID TARGET_IID
    ++++++++++++++++++++++++++++++
    11211161112211611123116211241003112101116101112102116101112103116102112201116201112202116201112203116202112301116301112302116301112302116302