RaqForum 25 No.
254 View  • 
     ...

3.6 Use concatenation and difference together

 
spl-cookbook(224)

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-cookbook(224)
 
 •  254 View  •