3.25 Union unordered table sequences through MERGE
Merge data in two unordered, same-structure tables, which may have common records, and calculate sum.
There are two sales tables db1 and db2 of same structure in the database, and we are trying to find the total sales amount in the year 2014.
| OrderID | Customer | SellerId | OrderDate | Amount |
|---|---|---|---|---|
| 10426 | GALED | 4 | 2014/01/27 | 338.2 |
| 10676 | TORTU | 2 | 2014/09/22 | 534.85 |
| 10390 | ERNSH | 6 | 2013/12/23 | 2275.2 |
| 10400 | EASTC | 1 | 2014/01/01 | 3063.0 |
| 10464 | FURIB | 4 | 2014/03/04 | 1848.0 |
| … | … | … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db1”).query@x(“select * from Sales”) |
| 2 | =connect(“db2”).query@x(“select * from Sales”) |
| 3 | =[A1,A2].merge@ou(OrderID) |
| 4 | =A3.select(year(OrderDate)==2014) |
| 5 | =A4.sum(Amount) |
A1 Retrieve Sales table from db1.
A2 Retrieve Sales table from db2.
A3 Use merge function to combine two tables by OrderID. @o option means the Sales tables are not necessarily ordered by order ID; @u option enables deleting duplicate records by order ID.
A4 Select records of the year 2014 from A3.
A5 Calculate total sales amount in the year 2014.
Execution result:
| Value |
|---|
| 723388.75 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
