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.

A1 Retrieve data from sub table file and generate cursor, in which data is filtered.
A2-A3 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

A2-A3 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.