"SQL WITH a AS( SELECT sum(Amount) Amount FROM Orders2021 WHERE Amount>500 or Quantity>50) .."

mars RaqForum 25 No.
268 View • 1 Years ago

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@x(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@x(CustomerID, EmployeeID, Amount,Quantity)
2	>Employees=file(“Employees.btx”).import@b().keys@i(EmployeeID)
3	=file(“Customers.ctx”).open().cursor@x(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)