...
5.2 Computing intersection and union
SQL
SELECT Distinct CustomerID
FROM Orders2020
UNION
SELECT Distinct CustomerID
FROM Orders2021
SPL
A | |
---|---|
1 | =file(“Orders2020_Account.ctx”).open().cursor(CustomerID).group@s(CustomerID;) |
2 | =file(“Orders2021_Account.ctx”).open().cursor(CustomerID).group@s(CustomerID;) |
3 | =[A1,A2].mergex@u(CustomerID) |
A1-A2 Retrieve data of two years respectively from the composite table file ordered by primary key and merger them. Note: When aggregate expression is absent, semicolon must be in place at the end of the parameter in order to perform aggregation.
A3 The mergex function combines two cursors through order-based merge, and returns cursor, from which data can be fetched as needed. @u option enables computing union.
SQL
SELECT Distinct CustomerID
FROM Orders2020
INTERSECT
SELECT Distinct CustomerID
FROM Orders2021
SPL
A | |
---|---|
1 | =file(“Orders2020_Account.ctx”).open().cursor(CustomerID).group@s(CustomerID;) |
2 | =file(“Orders2021_Account.ctx”).open().cursor(CustomerID).group@s(CustomerID;) |
3 | =[A1,A2].mergex@i(CustomerID) |
SQL
SELECT DISTINCT CustomerID
FROM Orders2020
EXCEPT --MINUS
SELECT Distinct CustomerID
FROM Orders2021
SPL
A | |
---|---|
1 | =file(“Orders2020_Account.ctx”).open().cursor(CustomerID).group@s(CustomerID;) |
2 | =file(“Orders2021_Account.ctx”).open().cursor(CustomerID).group@s(CustomerID;) |
3 | =[A1,A2].mergex@d(CustomerID) |