esProc Simplifies SQL-style computations – Ungrouping

During database application development, we often need to handle complicated SQL-style computations. One of them is the ungrouping, that is, the opposite process of grouping and summarizing which requires, for example, splitting each record into multiple records. We cannot realize the operation in SQL without taking great trouble. The code is so complicated and difficult to understand.

By contrast, esProc provides easier way and thus simpler code for realizing the ungrouping. The following are two examples.

In packGather, a table that holds the summarized data of packages of various products, productID field represents the ID numbers of products, packing field represents the number of each product's packages, all of which have the same number of products, and quantitySum represents the total quantity of a product. Some of the data are as follows:

We are asked to split packGather into tables of packages, that is, list packages separately and number each of them. Take product b as an example, there are 3 records in the table after it is split.

The following esProc code can be used to solve the problem:

A1：Select all the data from the database. They are as follows:

A2= A1.conj(packing.new(~:seq, productID:product, quantitiySum/packing:quan))

This line of code first splits each record in A1 into multiple records to create two-dimensional tables. Every two-dimensional table has different number of records but the same structure, with three fields: seq, product and quantity. Then it concatenates these two-dimensional tables to form a general two-dimensional table.

The function of conj function is to concatenate data. For example, split the first record in A1. The corresponding code and result are as follows:

A1(1).(packing.new( ~:seq,productID:product,quantitiySum/packing:quan)) 

A1.conj(…) is equal to [A1(1),A1(2),A1(3)…].conj(…). The final result is as follows:

Please note the expression packing.new(…), which means creating a new table sequence according to the packing field of each record in A1. new function is used to create a new table sequence based on an existing sequence or table sequence, like ["a","b","c"].new() or [1,2,3…N].new(). The latter can be abbreviated to N.new(). If, for example, the value of packing field in the first record is 2, this expression will be parsed as [1,2].new(…). While creating a new table sequence using new function, we can use "~" to represent members of the original sequence. So ~:seq in the expression in A2 means using the original sequence as the first field of the new table sequence, with seq being the field name.

A2 represents the final result of this example.

Now let’s look at another example that computes liquidated damages in a database. Here is a table – contract – that has multiple fields, three of which are ID (contract number), enddate (ending date) and amount (contract amount). Please compute how much liquidated damages should be paid each day for each breach of contract, on the assumption that the required liquidated damages per day is one thousandth of the contract amount.

Some of the data of contract are as follows:

esProc code:

This piece of code uses periods function to generate a sequence of dates starting from the ending date of a contract to the current date. # represents the current sequence number in the time sequence. The final result is as follows:

Note: 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. Please refer to the related documents for details.