"SQL SELECT Distinct CustomerID FROM Orders2020 UNION SELECT Distinct CustomerID FROM Orders2021 .."

mars RaqForum 25 No.
262 View • 1 Years ago

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@x(CustomerID).group@s(CustomerID;)
2	=file(“Orders2021_Account.ctx”).open().cursor@x(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@x(CustomerID).group@s(CustomerID;)
2	=file(“Orders2021_Account.ctx”).open().cursor@x(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@x(CustomerID).group@s(CustomerID;)
2	=file(“Orders2021_Account.ctx”).open().cursor@x(CustomerID).group@s(CustomerID;)
3	=[A1,A2].mergex@d(CustomerID)