Sort Records in a User-defined Order

Question

PRODUCT_ID, PRODUCT_NAME

100 Nokia

200 IPhone

300 Samsung

400 LG

 

I want to display the records like below:

 

PRODUCT_ID, PRODUCT_NAME

300 Samsung

200 IPhone

100 Nokia

400 LG

 

Answer

A user-defined sorting condition is always temporary and sometime long. To handle this type of scenarios, SPL is an expert with simple and easy to understand code:

 

A

1

=db.query(“select * from   t1”)

2

=A1.align@s([300,200,100,400],PRODUCT_ID)

A2: align() function sorts A1’s table sequence according to the specified order (align it to the specified sequence). A user-defined sorting condition (here [300,200,100,400]) can be an external parameter (list, for example), then A2 can be rewritten as A1.align@s(list,PRODUCT_ID).

undefined

esProc offers JDBC interface to let another program to call an SPL script in the way they call a database result set. To learn more about the invocation, refer to How to Call an SPL Script in Java.