11.1 Recursively find single references
Perform a self-join on a single table and recursively get a specified field’s all levels of references on a certain record.
Based on the Organization table, find all superior organizations of Beijing Market Research Team.
| ID | ORG_NAME | PARENT_ID |
|---|---|---|
| 1 | Head Office | 0 |
| 2 | Beijing Branch Office | 1 |
| 3 | Shanghai Branch Office | 1 |
| 4 | Chengdu Branch Office | 1 |
| 5 | Beijing R&D Center | 2 |
| … | … | … |
The rvs() function is used to reverse a sequence.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from Organization”) |
| 3 | >A2.switch(PARENT_ID,A2:ID) |
| 4 | =A2.select@1(ORG_NAME==“Beijing Market Research Team”) |
| 5 | =A4.prior(PARENT_ID) |
| 6 | =A5.rvs().(ORG_NAME).concat("/") |
A1 Connect to the database.
A2 Query Organization table.
A3 Map the foreign key PARENT_ID to the record it settles to achieve a self-join.
A4 Get the record of Beijing Market Research Team.
A5 Use prior() function to search for the superior organization.
A6 Use rvs() function to arrange all superior organizations from high to low.
Execution result:
| Value |
|---|
| Head Office / Beijing Branch Office / Beijing Marketing Department / Beijing Market Research Team |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
