DEV Community

Judy
Judy

Posted on

2 2 2 2 2

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
Oracle的SQL:

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

Image of Docusign

Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more

Top comments (1)

Collapse
 
esproc_spl profile image
Judy • Edited

Learn more about open source SPL:github.com/SPLWare/esProc

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay