【Question】

I have a bit weird scenario where I need to fetch data. I have the following three products:

product1

product2

product3

Each product has different ids (e.g. p1345, p3453, p2345) and different options which are having different skus:

option1(sku234),option2(sku345)

option1(sku1001)

option1(sku0022),option2(sku0033)

So if I store products in one CSV file and options in another CSV file, how can I fetch the relevant skus from the option file?

【Answer】

The first and most natural solution is to JOIN the two files and get skus through a computed column. Java takes a roundabout route to achieve this, yet it’s much simpler to express the algorithm in SPL (Structured Process Language):

A1: Read in each line of product.csv as a member and return a sequence.

A2: Read in each line of option.csv as a member and return a sequence.

A3: Split skus from each member of A2’s sequence and join them with A1.

A4: Create a new table sequence. Join each product with the corresponding skus respectively and populate values to the new table sequence.

The SPL script can be easily integrated into a Java program through esProc JDBC. Details are explained in How to Call an SPL Script in Java.