# * In a Multilevel Relationship, How to Replace Null Values at Nodes by Corresponding Values at Their Parent Nodes in SQL？

We have a database table EXAMPLE, which has data as follows:

 PK FK PARENT 001 23 000 002 null 001 003 46 001 004 12 000 005 null 004 006 null 005

Some records have nulls under FK field. We are trying to replace each null with “FK value of the direct parent node”, and write the corresponding PK value in the indent format at output. If the parent node is also node, find the FK value recursively upward. Below is the desired result:

 PK FK PARENT 001 23 000 002 23 001 003 46 001 004 12 000 005 12 004 006 12 005

OracleSQL

SELECT

LPAD(' ',LEVEL) || PK AS PK,

NVL(FK, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(FK,'/'),'(\d+)/*\$',1,1,'',1)) AS FK,

PARENT

FROM EXAMPLE

START WITH PARENT='000' CONNECT BY PRIOR PK = PARENT;

A recursive operation is needed here to replace the current FK value with that in the direct parent node. It is not very hard to achieve a recursive query in Oracle. The true difficulty lies in the subsequent computations, where the FK value on the superior node will be referenced. As SQL does not have concepts of explicit records and reference, it uses the regular expression to handle strings generated according to the recursive relationship. That is too hard.

It is easy to code the task in the open-source esProc SPL, without using the difficult regular expression:

 A 1 =connect("oracle") 2 =A1.query@x("SELECT * FROM EXAMPLE") 3 >A2.switch(PARENT,A2:PK) 4 >A2.run(p=~.prior(PARENT),PK=fill(" ",p.len())+PK,FK=p.(FK).ifn()) 5 >A2.run(PARENT=if(PARENT==null,"000",trim(PARENT.PK)))

SPL supports explicit records to be able to convert the referencing foreign key into a record type field, making it convenient to handle subsequent computations after the recursive operation.