11.9 Search for the upper-level reference
Perform a self-join on a single table and search for the record’s upper-level reference.
Find the upper-level region name for each administrative region based on China administrative division table.
ID | NAME | PARENT_ID |
---|---|---|
1 | China | 0 |
11 | Beijing | 1 |
12 | Tianjin | 1 |
13 | Hebei | 1 |
… | … | … |
1301 | Shijiazhuang | 13 |
1302 | Tangshan | 13 |
… | … | … |
SPL offers P.nodes(F) function to get a record’s upper-level reference.
SPL script:
A | |
---|---|
1 | =connect(“db”) |
2 | =A1.query@x(“select * from ChinaRegion”) |
3 | >A2.switch(PARENT_ID,A2:ID) |
4 | =A2.nodes(PARENT_ID) |
5 | =A4.new(ID,NAME,PARENT_ID.NAME:PARENT_NAME) |
A1 Connect to the database.
A2 Query ChinaRegion table.
A3 Map foreign key PARENT_ID to the corresponding record to achieve self-join.
A4 Use nodes() function to find the record’s upper-level refence.
A5 Create a table sequence consisting of ID, region name and upper-level region name.
Execution result:
A4:
ID | NAME | PARENT_ID |
---|---|---|
1 | China | (null) |
11 | Beijing | [1,China,] |
12 | Tianjin | [1,China,] |
13 | Hebei | [1,China,] |
… | … | … |
1301 | Shijiazhuang | [13,Hebei,] |
1302 | Tangshan | [13,Hebei,] |
1303 | Qinhuangdao | [13,Hebei,] |
… | … | … |
130102 | Changan District | [1301,Shijiazhuang,] |
130104 | Qiaoxi District | [1301,Shijiazhuang,] |
130105 | Xinhua District | [1301,Shijiazhuang,] |
… | … | … |
A5:
ID | NAME | PARENT_NAME |
---|---|---|
1 | China | (null) |
11 | Beijing | China |
12 | Tianjin | China |
13 | Hebei | China |
… | … | … |
1301 | Shijiazhuang | Hebei |
1302 | Tangshan | Hebei |
1303 | Qinhuangdao | Hebei |
… | … | … |
130102 | Changan District | Shijiazhuang |
130104 | Qiaoxi District | Shijiazhuang |
130105 | Xinhua District | Shijiazhuang |
… | … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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