7.14 Convert foreign key values to matching records
Based on two associated tables, join the associated records and perform computations on the joined table. It is possible that the two tables are not completely matching.
Calculate the total income of each employee according to the associated Employee table and PostAllowance table.
SPL uses A.switch() function to convert foreign key field values to matching records of foreign key table. Set a record as null when it does not have a match in the foreign key table.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Employee”) |
| 3 | =A1.query@x(“select * from PostAllowance”) |
| 4 | =A2.switch(Post, A3:Post) |
| 5 | =A4.new(ID,Name,Salary+Post.Allowance:Salary) |
A1 Connect to the database.
A2 Query Employee table.
A3 Query PostAllowance table.
A4 Use A.switch() function to convert Post field values in Employee table to matching records of PostAllowance table, during which a record that does not have a match in the second table is recorded as null.
A5 Create a new table sequence and calculate income of each employee.
Execution result:
| ID | Name | Salary |
|---|---|---|
| 1 | Rebecca | 8000 |
| 2 | Ashley | 12000 |
| … | … | … |
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
