...

3.7 Concatenate all set members in a sequence

 
spl-cookbook(224)

Calculate concatenation during aggregation on a sequence.

Order table and OrderDetail table has the relationship of primary table vs sub table. Each order corresponds to multiple detail records, as shown below:

Order
ID*
Customer
Date
OrderDetail
OrderID*
Number*
Product
Amount

Not all OrderDetail records for one order have same length. We are trying to get the following query result:

ID Customer Date Product1 Amount1 Product2 Amount2 Product3 Amount3
1 3 20190101 Apple 5 Milk 3 Salt 1
2 5 20190102 Beef 2 Pork 4
3 2 20190102 Pizza 3

SPL script:

A
1 =connect(“db”)
2 =A1.query@x(“select * from OrderDetail left join Order on Order.ID=OrderDetail.OrderID”)
3 =A2.group(ID)
4 =A3.max(~.count()).(“Product”+string(~)+“,”+“Amount”+string(~)).concat@c()
5 =create(ID,Customer,Date,${A4})
6 >A3.run(A5.record([ID,Customer,Date]|~.([Product,Amount]).conj()))

A1 Connect to the database.
A2 Query OrderDetail and Order by joining them on order ID.
A3 Group the retrieved records by order ID.
A4 Find the largest number of records in a group and generate the data structure string.
A5 Create a table sequence according to A4’s data structure.
A6 Loop over each group to get concatenation of records consisting of Product and Amount in each group using conj function, and append these records to A5’s table sequence.

 

spl-cookbook(224)
 
 •  165 View  •