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(CustomerID, City; Region==“SP”) |
| 2 | =file(“Orders2021_Account.ctx”).open().news(A1,Amount,ProductID,City) |
| 3 | =A2.groups(City,ProductID; sum(Amount):Amount) |
A1 Retrieve data from primary table and generate cursor, in which filtering is performed.
A2 Retrieve data from sub table file, use news function to associate with the primary table cursor while referencing primary table field ShipVia, 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(CustomerID, City; Region==“SP”) |
| 2 | =file(“Orders2021_Account.ctx”).open().news@r(A1,sum(Amount):Amount,City) |
| 3 | =A2.groups(City; sum(Amount):Amount) |
A2 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 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
