11.11 Search for all upper-level references
Perform self-join on a single table and find all upper-level references.
Based on China administrative division table, list all superior regions for each division. For Shijiazhuang, for instance, the output is China,Hebei,Shijiazhuang.
| ID | NAME | PARENT_ID |
|---|---|---|
| 1 | China | 0 |
| 11 | Beijing | 1 |
| 12 | Tianjin | 1 |
| 13 | Hebei | 1 |
| … | … | … |
| 1301 | Shijiazhuang | 13 |
| 1302 | Tangshan | 13 |
| … | … | … |
SPL uses P.nodes@p(F,r) function to search for all upper-level references.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from ChinaRegion”) |
| 3 | >A2.switch(PARENT_ID,A2:ID) |
| 4 | =A2.nodes@p(PARENT_ID) |
| 5 | =A4.run(=.(NAME).concat@c()) |
A1 Connect to the database.
A2 Query ChinaRegion table.
A3 Map foreign key PARENT_ID to the corresponding record to achieve self-join.
A4 The nodes() function works with @p option to recursively search for all upper-level references.
A5 Concatenate upper-level reference names, separated with comma.
Execution result:
A4:
| Member |
|---|
| [[1,China,]] |
| [[1,China,],[11,Beijing,]] |
| [[1,China,],[12,Tianjin,]] |
| [[1,China,],[13,Hebei,]] |
| … |
| [[1,China,],[13,Hebei,],[1301,Shijiazhuang,]] |
| [[1,China,],[13,Hebei,],[1302,Tangshan,]] |
| [[1,China,],[13,Hebei,],[1303,Qinhuangdao]] |
| … |
| [[1,China,],[13,Hebei,],[1301,Shijiazhuang,],…] |
| [[1,China,],[13,Hebei,],[1301,Shijiazhuang,],…] |
| [[1,China,],[13,Hebei,],[1301,Shijiazhuang,],…] |
| … |
A5:
| Member |
|---|
| China |
| China,Beijing |
| China,Tianjin |
| China,Hebei |
| … |
| China,Hebei,Shijiazhuang |
| China,Hebei,Tangshan |
| China,Hebei,Qinhuangdao |
| … |
| China,Hebei,Shijiazhuang,Chang’an District |
| China,Hebei,Shijiazhuang,Qiaoxi District |
| China,Hebei,Shijiazhuang,Xinhua District |
| … |
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
