6.2 Application scenarios

 

SQL

WITH a AS(
    SELECT sum(Amount) Amount
    FROM Orders2021
    WHERE Amount>500 or Quantity>50)
SELECT month(OrderDate) m,sum(Orders2021.Amount)/a.Amount p
FROM Orders2021,a
WHERE Orders2021.Amount>5000
GROUP BY month(OrderDate),a.Amount
ORDER BY month(OrderDate)

SPL

A B
1 =file(“Orders2021_Account.ctx”).open().cursor(OrderDate,Amount,Quantity)
2 cursor A1 =A2.select(Amount>500 || Quantity>50).total(sum(Amount))
3 cursor =A3.select(Amount>5000).groups(month(OrderDate):m; sum(Amount):p)
4 >A3.run(p=p/A2)

A2 uses key word cursor to define a channel which is same as A1’s cursor. B2 specifies the first grouping & aggregation in A2’s channel.
A3 Define a channel.
B3 Specify the second grouping & aggregation in A3’s channel.


SQL

SELECT coalesce (s.City, b.City ) City,b.Amount ba,s.Amount sa
FROM (
    SELECT City,sum(Amount) Amount
    FROM Orders2021
    LEFT JOIN Employees ON Orders2021.EmployeeID=Employees.EmployeeID
    WHERE Amount>5000 or Quantity>50
    GROUP BY City) b
FULL JOIN (
    SELECT City,sum(Amount) Amount
    FROM Orders2021
    LEFT JOIN Customers ON Orders2021.CustomerID=Customers.CustomerID
    WHERE Amount<=1000 or Quantity<5
    GROUP BY City) s
ON b.City=s.City

SPL

A
1 =file(“Orders2021_Account.ctx”).open().cursor(CustomerID, EmployeeID, Amount,Quantity)
2 >Employees=file(“Employees.btx”).import@b().keys@i(EmployeeID)
3 =file(“Customers.ctx”).open().cursor(CustomerID,City)
4 >ch=channel(A1).select(Amount>5000 || Quantity>50). switch(EmployeeID,Employees:EmployeeID).groups(EmployeeID.City;sum(Amount):Amount)
5 =A1.select(Amount<=1000 || Quantity<5)
6 =joinx@1(A5:primary, CustomerID; A3:sub, CustomerID)
7 =A6.groups(sub.City; sum(primary.Amount):Amount)
8 =ch.result()
9 =join@f(A8:b,City;A7:s,City)
10 =A9.new([b.City,s.City].nvl():City,b.Amount:ba,s.Amount:sa)

A4 Define a channel and groupings & aggregations on them.
A5-A7 Perform groupings & aggregations on cursor.
A8 Get results from the channel.