Examples of esProc’s Assisting in Dynamic-Column-SQL Computations

The SQL implemented by database vendors can be used to write dynamic statements. But since it’s inconvenient to perform set operations and order-related computations in SQL, usually you must resort to the high-level languages to handle the dynamic-column computations, which leads to a cumbersome process of doing these computations.

As esProc (free edition is available) supports dynamic scripting, order-related calculations and set operations, it can make the task easier. Besides, esProc can operate independently, as well as support console execution, invocation by reporting tools and Java applications. More details can be found in How esProc Assists Writing SQL Queries.

The following discusses the common problems involving dynamic-column computations through examples, and gives their esProc solutions. Transposition is a special kind of dynamic-column computation. Since you can find detailed explanation about it in The Standard esProc Method of Assisting SQL-style Transposition, there’s no need to discuss it in this article.

Sorting values in different columns
The order table has 6 integer type fields and you need to sort field values in each record in ascending order. Below is the source data:

esProc script:

Retrieve data with a SQL statement and then handle the records one by one. The process of handling is to convert field values of the current record to a set using array function; sort the set using sort function; and finally enter the re-sorted field values into the current record using record function. Here’s the result:

Generating dynamic MERGE statement

The parameters source and target represent two tables, with the same structure but different data, in MSSQL database. You need to update the target table with the source table according to the primary key. For instance, the primary key of both table1 and table2 is A and B. The source data is as follows:

To update table1 with table2, you need to write the following dynamic MERGE statement:

MERGE INTO table1 as t

USING table2 as s

ON t.A=s.A and t.B=s.B

WHEN MATCHED

THEN UPDATE SET t.C=s.C,t.D=s.D

WHEN NOT MATCHED

THEN INSERT VALUES(s.A,s.B,s.C,s.D)

Below is the updated table1:

esProc script:

Retrieve the primary key as well as the other fields of the system table represented by source, and store them respectively in two variables – pks and columns; and then use the loop function pks.(…) to generate the MERGE statement dynamically. ~ represents a loop variable and # represents the loop number.

The above script is only responsible for generating the MERGE statement, which can be returned to JAVA for execution or be executed by esProc alone using A6=myDB1.excute(A5) .

Dynamic MERGE of tables from different databases

The source table and the target table in the preceding example are in the same database, but if they belong to different databases (say MSSQL and Oracle), you can’t compose the MERGE statement in SQL. Yet you can use esProc to help implement the MERGE, using the following code:

myDB2 and myDB1 are the databases holding source and target. A3 and A4 open the databases as cursors and then join@x1 function is used to perform a join between the cursors. After that a loop is run to fetch a batch of data from A5 into the memory and update the target using update function. @i means generating only the insert statements; @u means generating only the update statements. Below is the target table after the code is executed:

This solution works just as well with databases that don’t support MERGE statement, such as MySQL.

Dynamic SQL generated from a table containing table names
The table A has two fields – ID and TableName in which names of some other tables are stored, such as B , C and D. These tables have the same structure and use ID as the key field. The ID field in table A is the IDs of other tables. Below are the source tables:

You need to get the records of those other tables according to table A. Here’s the result:

esProc script:

Retrieve data from table A and group it by TableName; loop through every group to get the corresponding record in another table dynamically; finally concatenate the records and sort them by ID. Here’s the result:

If table A is unordered but the result is required to be in line with its order, you need to use the following code: A1.(mssql.query(“select ID,Num from “+TableName+” where ID =?”,ID)).conj() .

Dynamic inter-row aggregation

The number of fields in the table tb is unknown but the fields contain the same type of data. You need to count the value types and calculate the number of each type of value among all the fields of all the records. Below is the source data:

esProc script:

Concatenate the filed values of each record into a set and perform group and aggregate. Here’s the result:

Finding different values from two records

The Burger table stores the quantities of ingredients of the hamburgers in two tests. You need to compare the two records and lists the ingredients with different quantities. Below is the source data:

esProc script:

A2 and A3 convert the two records into two sets; get a list of sequence numbers of the different members between A2 and A3 using pselect function; and finally create a new two-dimensional table according to the field order. Here’s the result:

If the source data contains multiple groups of data and each has two records, three fields – name, fieldname and value – need to be generated to compose the result of the comparison. To do this, use the following code:

Group the data by names and get different ingredients from each group and append them to B1. Here’s the result: