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