We are often confronted with complicated SQL-style computations during developing data base applications. For instance, arrange grouped data into one column in certain order, making marks for different types of groups and detail data, as shown in the following:
This kind of operation is commonly seen in organizing report data. As SQL lacks the mechanism of ordered sets, we need to create computed columns for sorting based on group and detail data, combine the group and the detail data together and finally sort them. Usually, special SQL functions of non-ANSI standard are used to realize the operation, which complicates the code writing and produces difficult-to-understand code. The operation will be made even more difficult if we try to combine multilayered groups together.
However, we can use esProc to perform this kind of operation without creating computed columns and with concise and simple code. The method will be explained in detail through the following example.
Computing target:
Arrange the branch stores and their corresponding DVD copies in one column according to DVDCopy table.
Data Structure:
The first three fields of DVD table are CopyID, DVDID and BID, which represent the ID numbers of DVD copies, DVDs and branch stores respectively. There is a many-to-one relationship between DVD copies and a branch store. Part of the data is as follows:
The computed result should be like this:
Code written in esProc:
| A | B | C | |
| 1 | =db.query(“select * from DVDCopy order by BID”) | ||
| 2 | =create(value,type) | ||
| 3 | for A1 | if A3.BID!=B2 | =A2.insert(0,A3.BID,”Branch”) |
| 4 | >B2=A3.BID | =A2.insert(0,A3.CopyID,”Copy”) |
A1： Retrieve data from the database and sort them by BID field. The result is as follows:
A2：=create(value,type) aims to create an empty table sequence A2 having two fields: value and type.
A3-C4：Traverse the data in A1 and insert BID and CopyID into A2. Detailed steps: If BID of the current record is changed (what the code in B3 represents), insert a branch store record into A2 (what the code in C3 represents); modify BID of the current record to B2 (what the code in B4 represents) in order to see if BID will change in the next record; insert a DVD copy record (what the code in C4 represents).
for A1 in the code in A3 represents traversing by loop during which one record will be fetched from A1 each time. The current record, like A3.BID, can be accessed by the variable A3 in the loop body. The working scope of a loop statement can be represented directly by the indentation of cells, such as the working scope of A3 is B3-C4.
After the traversing by loop is over, the final computed result will be stored in A2, as shown in the following figure:
Further discussion: Move more layers of data into one column
In the previous example, there are only two layers of data: BID and CopyID. Actually BID, DVDID and CopyID can form three-layer data. In a similar way, we can also combine the three-layer data into one column in esProc. The code is as follows:
| A | B | C | |
| 1 | =db.query(“select * from DVDCopy order by BID,DVDID”) | ||
| 2 | =create(value,type) | ||
| 3 | for A1 | if A3.BID!=B2 | =A2.insert(0,A3.BID,”Branch”) |
| 4 | >B2=A3.BID | ||
| 5 | if A3.DVDID!=C2 | =A2.insert(0,A3.DVDID,”DVD”) | |
| 6 | >C2=A3.DVDID | =A2.insert(0,A3.CopyID,”Copy”) |
Computed result is as follows:
In addition, esProc can be called by the reporting tool or a Java program. The method is similar to that with which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to Java main program. For more details, please refer to the related documents