You can perform data sorting in
both the report script and in SQL. But both are only capable of sorting data by
specified field(s). To sort data by a specified list, you have to compose the
code using functions like decode and union. If the sorting criterion is a
dynamic parameter, it’s more difficult to implement it in SQL or with a report
script as a temporary table is usually needed. What’s worse, the sorting
criterion doesn’t always correspond to the field values completely. Sometimes
you need to fill in the difference, while other times you can just ignore them.
It’s rather complicated in handling both issues.
With support of alignment
functions, order-related computations and explicit sets, esProc can easily
solve the sorting problems mentioned above. So you can use it as the tool for
report data source preparation. The reporting tool
regards an esProc script as the stored procedure, passes parameter to it and
gets the returned result after execution through JDBC. For more details, see Howto Use esProc to Assist Reporting Tools.
Here are the sorting problems concerning external/dynamic
criterion commonly seen in report development, and their solutions in esProc.
Sorting by simple external criterion
Sort the Service table according to the condition that the aNum field will be presented in an order
specified by [1,3,2,4], as shown by selections of source and target tables below:
Source
table
|
Target
table
|
||||||||||||||||||||||||||||||||||||||||||||||||
|
|
esProc code:
A
|
|
1
|
$select
name,aNum from tb
|
2
|
=A1.align@a([1,3,2,4],aNum).conj()
|
A1 performs a SQL statement. The align function groups and sorts records
according to the specified list, with the default rule that only the first
found record is retained; but with @a, the function will retrieve all records
in a group. The conj function
concatenates records together.
1. Dynamic
sorts can be achieved through a parameter, such as A2=A1.align@a(arg_List,
aNum).conj(). So when arg_List is [1,3,2,4], we can get our result. In
this way the code becomes reusable.
2. If
there are fewer items in the list than the aNum
field values, such as arg_List=[1,2,4], records that don’t have matching items
will be discarded by default, as shown by the following result:
To append those mismatched records after the sorted ones, use A2=A1.align@n(arg_List, aNum).conj() and the
result is as follows:
Or you can use the equal statement
- A2=A1.align@s(arg_List, aNum).
3. If
the items of the list outnumber the field values, the extra items won’t appear
in the sorting result.
Exporting data in specified order
Suppose we need to sort the
database table PRODUCT for presentation. Instead of performing the sort by a
field, the requirement is that the top N records be sorted in the specified
order and the rest by an existing field, as shown in the following selections
of source table and target table:
Source
table
|
Sort
the top 4 records in specified order
|
||||||||||||||||||||||||||||
|
|
esProc code:
A
|
|
1
|
$select
PRODUCT_ID,
PRODUCT_NAME from PRODUCT
|
2
|
=A1.align@s(arg_IDList,
PRODUCT_ID)
|
The align groups and sorts data according to the specified list, and @s
adds the mismatched records at the end. The above target table can be obtained
when arg_IDList is [300,400,100,200].
Finding missing values
There are a lot of records in Table1 where ID field is a sequence
consisting of inconsecutive integers. Now we need to find those missing
integers and put them in the right places in Table1, while leaving corresponding fields in blank. Below is a
selection of the source data:
ID
|
Name
|
Amount
|
1001
|
Jonathan
|
4418.6
|
1002
|
Timothy
|
3212.7
|
1003
|
Michael
|
2711.3
|
1007
|
Alexis
|
5322.4
|
1008
|
Ashley
|
832
|
1009
|
Daniel
|
3423.3
|
1010
|
Joseph
|
483.2
|
1012
|
Chloe
|
87332.2
|
If the set of integers is small, we
can generate a sequence with consecutive numbers in SQL with the minimum and
the maximum values, compute the difference between this sequence and the ID
field values using a subquery, then create empty records and union with the
original data, and finally perform a sort. But if the set is big, we need to look
for a workaround for implementing the merge algorithm to increase the
performance. As a result, the code becomes complicated. esProc supports a
direct merge by fields, and thus can produce the following code:
1
|
=db.query(“select
ID from table1 order by ID”)
|
2
|
=to(A1.m(1).ID,A1.m(-1).ID).new(~:ID,Name,Amount)
|
3
|
=[A1,A2].merge@u(ID)
|
The m function can get members of a set by their sequence numbers in
both normal and reverse orders. A1.m(1) an be simplified as A1(1). The to function generates a consecutive
sequence. The merge function merges
ordered data, and it works with @u to get the union. Here’s the result:
Intra-group sorting in a fixed order
The attendance table records the attendance
information. The requirement is to convert each person’s information per day (a
fixed 7 records) to two rows - one is morning and the other is afternoon for
reporting. For each row, Per_Code, Date, In and Out fields are the
same but Break and Return fields are not. Below is the attendance
information of a person on a certain day:
Per_Code
|
in_out
|
Date
|
Time
|
Type
|
1110263
|
1
|
2013-10-11
|
17:14.0
|
In
|
1110263
|
6
|
2013-10-11
|
37:00.0
|
Break
|
1110263
|
5
|
2013-10-11
|
38:21.0
|
Return
|
1110263
|
0
|
2013-10-11
|
43:21.0
|
NULL
|
1110263
|
6
|
2013-10-11
|
21:30.0
|
Break
|
1110263
|
5
|
2013-10-11
|
25:58.0
|
Return
|
1110263
|
2
|
2013-10-11
|
28:55.0
|
Out
|
According to
this data, we should retrieve the 4 records of the morning by the specified
sequence numbers and then transform them to a single record with static method.
The operation will be performed on records of both morning and afternoon
separately. There are a lot of real world cases that involve this kind of
order-related algorithm. But as SQL lacks the intrinsic sequence numbers, it
turns to pivot and over methods or the like. The problem is
the composed code is difficult to understand and debug.
esProc code:
A
|
|
1
|
=$select
* from attendance order by Per_Code,Date,Time
|
2
|
=A1.group(Per_Code,Date)
|
3
|
=A2.(~.align([1,7,2,3],#))
|
4
|
=AM=A3.new(Per_Code,
Date, ~.(Time)(1):In ,~.(Time)(2):Out, ~.(Time)(3):Break, ~.(Time)(4):Return)
|
5
|
=A2.(~.align([1,7,5,6],#))
|
6
|
=PM=A5.new(Per_Code,
Date, ~.(Time)(1):In ,~.(Time)(2):Out, ~.(Time)(3):Break, ~.(Time)(4):Return)
|
7
|
=AM |
PM
|
A3 retrieves
record 1, 7, 2 and 3 from each group. A4 joins and converts the four records
into a single record and stores it in the empty two-dimensional table sequence
AM. ~ represents the current group, # represents a sequence number in a group, and | concatenates
records together.
Here’s the
result for a person on a certain day: