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)