Simplified fixed sorting of SQL computing and simplified SQL sorting

Source: Internet
Author: User
Tags manual writing

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.