12.8 Judge nonexistence of foreign key mapping
Based on association of two tables, get records through judging nonexistence of foreign key mapping.
According to Sales table and Customer table, find total order amount of each new customer in the year 2014.
| Sales |
|---|
| ID |
| CustomerID |
| OrderDate |
| … |
| Customer |
|---|
| ID |
| Name |
| City |
| … |
The A.join() function works with @d option to get non-matching records only.
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.join@d(CustomerID ,A3:ID) |
| 5 | =A4.groups(CustomerID; sum(Amount):Amount) |
A1 Connect to the database.
A2 Get Sales records of the year 2014.
A3 Query Customer table.
A4 The A.join@d() function gets Sales records whose customer IDs do not exist in Customer table.
A5 Group join result by CustomerID and sum amounts for each customer.
Execution result:
| CustomerID | Amount |
|---|---|
| DOS | 11830.1 |
| HUN | 57317.39 |
| … | … |
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
