3.6 Use concatenation and difference together
Calculate concatenation and difference of multiple sets.
The task is to find branch stores that has less than 4 categories of DVD copies based on:
Branch table that stores information about DVD branch stores, DVD table that stores tiles of DVDs and category information, and DVDCopy table that stores information of DVD copies, whose physical entities are stored in different branch stores.
| Branch |
|---|
| BID |
| Street |
| City |
| DVDCopy |
|---|
| CopyID |
| DVDID |
| BID |
| Status |
| LastDateRented |
| LastDateReturned |
| MemberID |
| DVD |
|---|
| DVDID |
| Category |
| Title |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =Branch=A1.query(“select * from Branch”) |
| 3 | =DVD=A1.query(“select * from DVD”) |
| 4 | =DVDCopy=A1.query@x(“select * from DVDCopy”) |
| 5 | =DVDCopy.switch(DVDID,DVD:DVDID; BID,Branch:BID) |
| 6 | =DVDCopy.select(STATUS!=“Miss” && LASTDATERETURNED!=null) |
| 7 | =A6.group(BID) |
| 8 | =A7.select(~.icount(DVDID.CATEGORY)<4) |
| 9 | =A8.(BID) | (Branch \ A7.(BID)) |
A1 Connect to the data source.
A2 Import branch store data and define it as Branch variable.
A3 Import DVD data and define it as DVD variable.
A4 Import DVDCopy data and define it as DVDCopy variable.
A5 Transform values of DVDID field in DVDCopy table into corresponding records in DVD table and those of BID field into matching records in Branch table.
A6 Find missing and unreturned DVD copies.
A7 Gorup A6 by BID.
A8 Find branch stores that have less than 4 categories of DVD copies.
A9 Get branch stores that do not have all DVD copies. A8.(BID) gets branch stores that have less than 4 categories of DVD copies; Branch \ A7.(BID) finds branch stores that do not have any DVD copies.
Execution result:
| BID | STREET | CITY |
|---|---|---|
| B002 | Street2 | Houston |
| B003 | Street3 | LA |
| B004 | Street4 | Lincoln |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
