RaqForum 25 No.
442 View  • 
     ...

11.9 Search for the upper-level reference

 
spl-cookbook(224)

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-cookbook(224)
 
 •  442 View  •