5.4 Filtering primary table according to sub table
SQL
SELECT ProductID,City,sum(Amount) Amount
FROM Customers
INNER JOIN Orders2021 ON Orders2021.CustomerID=Customers.CustomerID
WHERE Orders2021.Quantity>=5
GROUP BY ProductID,City
SPL
When the filtering condition is specified on sub table and if there are a small number of records are left after sub table filtering, we can filter the primary table according to the sub table. The work procedure is similar to that of filtering sub table according to primary table. The only difference is that the latter also involves aggregation on primary key during filtering.
| A | |
|---|---|
| 1 | =file(“Orders2021_Account.ctx”).open().cursor(CustomerID,ProductID,Amount;Quantity>=5) |
| 2 | =file(“Customers.ctx”).open().new(A1,ProductID,City,sum(Amount):Amount) |
| 3 | =A2.groups(ProductID,City;sum(Amount):Amount) |
A1 Retrieve data from sub table file and generate cursor, in which data is filtered.
A2 Retrieve data from primary table file and use new function to associate with sub table cursor. During association, filter data according to sub table’s primary key, reference fields of the sub table while performing aggregation, and return cursor.
SQL
SELECT Region,ProductID,sum(Amount) Amount
FROM Customers
INNER JOIN Orders2021 ON Orders2021.CustomerID=Customers.CustomerID
WHERE Orders2021.Quantity>=5
GROUP BY Region, ProductID
SPL
| A | |
|---|---|
| 1 | =file(“Orders2021_Account.ctx”).open().cursor(CustomerID,ProductID,Amount;Quantity>=5) |
| 2 | =file(“Customers.ctx”).open().new@r(A1,Region,ProductID, Amount) |
| 3 | =A2.groups(Region, ProductID;sum(Amount):Amount) |
A2 As we need to group and aggregate data by sub table’s ProductID field, aggregation on join filed during association with primary table is wrong. The right way is to first perform association and then aggregation. To do this, use @r option in new function.
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
