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.

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:

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.