DEV Community


Posted on

In a Multilevel Relationship, How to Replace Null Values at Nodes by Corresponding Values at Their Parent Nodes

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

Image description
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:

Image description


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

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



Enter fullscreen mode Exit fullscreen mode

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:

Image description
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.

Top comments (1)

esproc_spl profile image
Judy • Edited

Learn more about open source