esProc Simplifies SQL-style Computations – Data Sorting by Specified Order

Data sorting by a specified order is one of the complicated SQL-style computations we often need to deal with during the database application development. The computation requires that data be sorted by a specified order rather than in ascending or descending order. For example, sort data according to the order of 2,3,1, or of "DSGC", "TAS", "GC","HU". If a table can be created to store the criteria in the database, we can sort data using JOIN statement. But in many cases the sorting criteria are dynamic, or in the database a table is not allowed to be created, thus we should perform UNION before JOIN, or use decode function. We can do so when the sorting criteria are short. Sometimes they are quite long, and we must write long UNION and decode strings manually, which causes mistakes frequently.

esProc, however, can perform this kind of computation easily. Let’s look at how it works through an example.

There is a table – LIQUORS – that holds inventory of a great many liquors. Now the records need to be sorted according to a specified LID order, like "2, 1 , 3, 4, 5...". Some of the data in table LIQUORS are as follows:

esProc code:

A1: Select all the data from LIQUORS. Some of the data are as follows:

A2=A1.align@s([2,1,3,4,5], LID). This line of code sorts LID field in the object of A1 according to the order of 2, 1, 3, 4, 5. The result is as follows:

The sorting criterion can also be an external parameter. For example, define a parameter – arg1, modify the code in A2 into =A1.align(arg1, LID), execute the script (which can be called by a Java program or a reporting tool as well) and enter [2,1,3,4,5] in the Input argument window, as shown below:

The computed result is completely the same as that got by executing the previous code:

In addition to solving problems of data sorting by a specified order, esProc can provide good solution to problems of data grouping with fixed criteria. As to more information in this respect, please refer to "esProc Simplifies SQL-style computations – Data Grouping with Fixed Criteria".

esProc program can be called by a reporting tool or a Java program in a way similar to that in which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a result of the form of ResultSet to the Java main program. For more details, please refer to the related documents.