* 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.

Q & A Collection

https://stackoverflow.com/questions/64128476/overwriting-nulls-with-parent-data-in-a-hierarchical-query