5.3 Filtering sub table according to primary table
SQL
SELECT City,ProductID, sum(Amount) Amount
FROM Customers
INNER JOIN Orders2021 ON Orders2021.CustomerID= Customers.CustomerID
WHERE Customers.Region='Sp'
GROUP BY City,ProductID
SPL
If there are a small number of remaining Orders records are left after filtering, we can filter the sub table according to the primary table. The method will filter the primary table, locate records according to primary table’s primary key, perform association and export cursor.
| A | |
|---|---|
| 1 | =file(“Customers.ctx”).open().cursor@x(CustomerID, City; Region==“SP”) |
| 2 | =file(“Orders2021_Account.ctx”).open() |
| 3 | = A2.news(A1,Amount,ProductID,City) |
| 4 | =A3.groups(City,ProductID; sum(Amount):Amount) |
| 5 | =A2.close() |
A1 Retrieve data from primary table and generate cursor, in which filtering is performed.
A2-A3 Retrieve data from sub table file, use news function to associate with the primary table cursor while referencing subtable fields Amount, ProductID, and City, and return cursor.
SQL
SELECT City, sum(Amount) Amount
FROM Customers
INNER JOIN Orders2021 ON Orders2021.CustomerID= Customers.CustomerID
WHERE Customers.Region='Sp'
GROUP BY City
SPL
| A | |
|---|---|
| 1 | =file(“Customers.ctx”).open().cursor@x(CustomerID, City; Region==“SP”) |
| 2 | =file(“Orders2021_Account.ctx”).open() |
| 3 | =A2.news@r(A1,sum(Amount):Amount,City) |
| 4 | =A3.groups(City; sum(Amount):Amount) |
| 5 | =A2.close() |
A2-A3 Here we just perform an aggregation on City field, which is determined by CustomerID. So, we use @r option to first summarize the sub table during association. This can reduce the number records to be generated.
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
