Examples of Dynamic-Column-SQL Computations in JAVA

It’s tedious and cumbersome to express dynamic columns in SQL, so programmers usually turn to high-level languages like JAVA to compose the dynamic statement. The problem is JAVA basic class libraries don’t include set operations, causing equal amount of difficulty for those trying to do it automatically.

esProc supports dynamic scripting and set operations, making the task easier if you use it as the JAVA class library. A JAVA application can access the esProc computed result through JDBC. More details can be found in How to Use esProc As the Class Library for Java.

The following discusses the common JAVA problems involving dynamic-column computations through examples, and provides their esProc solutions. Transposition is a special kind of dynamic-column computation, which you can find detailed explanation about it in The Standard esProc Method of Assisting SQL-style Transposition, so we won’t discuss it here.

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: