SPL: Recursively Search Referenced Records

SPL: Recursively Search Referenced Records

Recursion refers to an operation or function that calls itself directly or indirectly. For example, the tower of Hanoi is a typical recursive operation. It is also very common to use recursion for queries in practice. For instance, we want to know the authorities that are in charge of a certain office. The superior authority that directly dominates the office is not difficult to find. However, the uncertain layers of affiliations are hard to find if we continue to query the superiors of the relative branches repeatedly, and that’s where recursion comes in.

1. Recursively search all referenced records

[e.g. 1] Query the level of each office based on the organizational structure table of the company (the head office is level 1, branch office is level 2, and so forth). Some of the data are as follows:

IDORG_NAMEPARENT_ID
1Head Office0
2Beijing Branch Office1
3Shanghai Branch Office1
4Chengdu Branch Office1
5Beijing R&D Center2

Every record needs to be looped to recursively find all the superiors of each office. The A.prior(F) function in SPL recursively searches referenced records, and by default, all of the referenced records.

The SPL script is as follows:

A
1=T("Organization.txt")
2>A1.switch(PARENT_ID,A1:ID)
3=A1.new(ID,ORG_NAME,~.prior(PARENT_ID).len():LEVEL)

A1: import the organizational structure table.

A2: objectify the parent ID foreign key as the corresponding parent office record for self-join

A3: create a new table consists of sequence numbers, organization names, and levels, in which the levels of offices are calculated by A.prior() function with the layer numbers of the referenced records recursively.

2. Recursively search referenced records until the specified value

[e.g. 2] Query the affiliated offices of Beijing Branch Office and list the names of its superior offices, separated by commas with multi-layers, based on the organizational structure table of the company. Here are some of the data:

IDORG_NAMEPARENT_ID
1Head Office0
2Beijing Branch Office1
3Shanghai Branch Office1
4Chengdu Branch Office1
5Beijing R&D Center2

In this example, we want to terminate the recursion and save the current office when the specified value (Beijing Branch Office) is found and skip the offices that cannot be found. The A.prior(F,r) function in SPL recursively searches referenced records until the specified value r.

The SPL script is as follows:

A
1=T("Organization.txt")
2>A1.switch(PARENT_ID,A1:ID)
3=A1.select@1(ORG_NAME=="Beijing Branch Office")
4=A1.new(ID,ORG_NAME,~.prior(PARENT_ID,A3):PARENT_NAME)
5=A4.select(PARENT_NAME!=null)
6=A5.run(PARENT_NAME=PARENT_NAME.(PARENT_ID.ORG_NAME).concat@c())

A1: import the organizational structure table.

A2: convert the parent ID to the corresponding parent office record to objectify the foreign key.

A3: select the record of Beijing Branch Office.

A4: create a new table consisting of sequence numbers, organization names, and the record set of all the superiors.

A5: select the record of non-empty parent office, that is, the record of Beijing Branch Office.

A6: loop the names of parent offices to concatenate them as a string separated by commas.

3. Recursively search leaf records

[e.g.3] Query the following counties of Hebei Province according to the Chinese administrative region table. Here are some of the data:

IDNAMEPARENT_ID
1China0
11Beijing1
12Tianjin1
13Hebei1
1301Shijiazhuang13
1302Tangshan13

On the contrary to the previous sections, this example searches all the leaf records based on the parent records. The P.nodes@d(F,r) function queries to search all of the leaf records recursively.

The SPL script is as follows:

A
1=T("ChinaRegion.csv")
2>A1.switch(PARENT_ID,A1:ID)
3=A1.select@1(NAME=="Hebei")
4=A1.nodes@d(PARENT_ID,A3)
5=A4.new(ID,NAME,PARENT_ID.NAME:PARENT_NAME)

A1: import the Chinese administrative region table.

A2: convert the parent ID to the corresponding parent region record to objectify the foreign key.

A3: select the record of Hebei Province.

A4: search recursively with P.nodes() function, and @d is used to recursively search all the leaf records.

A5: create a table consisting of sequence numbers, county names, and province names.

Leave a Reply