7.16 Judge data nonexistence through foreign key mapping
Get records that do not have matches in the foreign key table based on two associated tables.
Find the new customers in the year 2014 according to the associated Sales table and Customer table.
A.switch() function works with @d function to only retain records that do not have matches in the right table. In this case the foreign key field value in each record won’t be recorded as null.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Sales where year(OrderDate)=2014”) |
| 3 | =A1.query@x(“select * from Customer”) |
| 4 | =A2.switch@d(CustomerID ,A3:ID) |
A1 Connect to the database.
A2 Get Sales records of the year 2014.
A3 Query Customer table.
A4 Use switch@d() function to get records whose CustomerIDs do not exist in Customer table.
Execution result:
| ID | CustomerID | OrderDate | … |
|---|---|---|---|
| 10439 | MEREP | 2014/02/07 | … |
| 10504 | WHITC | 2014/04/11 | … |
| … | … | … | … |
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
