Simplified fixed sorting of SQL computing and simplified SQL sorting
In database application development, we often have to deal with complicated SQL-based computing. Fixed sorting is one of them. Fixed sorting is not in ascending or descending order of a field, but in a fixed order, such as sorting by 2, 3, 1, or by "DSGC", "TAS ", sort by GC and HU. If you can create a table in the database to store the data, you can use the join statement to sort the data. However, in many cases, the sorting depends on changes, or the database cannot create a table. In this case, union is required before join, or the decode function is used. This can be done when the sorting basis is relatively short, but sometimes the sorting basis is very long, it will lead to the manual writing of a long union string and decode string, the error rate will be very high.
The Set calculator can easily implement fixed sorting. The following example is used to describe it.
The LIQUORS table records a large number of wine inventories and now needs to be sorted by a fixed LID order, such as "2, 1, 3, 4, 5 ...". Some data of the LIQUORS table is as follows:
Code of the Set calculator:
A1: Number of retrieved data from the database. Some results are as follows:
A2 = A1.align @ s ([2, 1, 3, 4, 5], LID) the code sorts the LID fields in the A1 object in the order of 2, 1, 3, 4, and 5. The result is as follows:
The preceding sorting can be based on external parameters. For example, you can define an arg1 parameter and change the code in A2 to: = A1.align (arg1, LID ). Run the script (you can also call it through JAVA or report). On the parameter interface, enter [2, 1, 3, 4, 5], as follows:
The calculation result is exactly the same as before.
The Set calculator not only solves the problem of fixed sorting, but also supports fixed grouping. For details, refer to "Simplifying fixed grouping for SQL computing".
In addition, the assembler can be called by report tools or java programs. The called method is similar to that of common databases, you can use the JDBC interface provided by it to return the computing results in the form of ResultSet to the java main program or report tool. For details, refer to the relevant documentation.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.