Transforming Records with esProc

Problem source：http://bbs.csdn.net/topics/390991336

Table 1 (T1) stores single-value id field and name field. A selection of the source data is as follows:

id    name

1     a

2     b

3     c

4     d

5     e

Table 2 (T2) stores multi-value ids field as follows:

ids

1,2,3

4,3

1,2,3

According to T1’s id field and name field, we want to transform T2’s ids field into a field with values being the corresponding names. The transformed T2 is expected to be like this:

a,b,c
d,c
a,b,c

esProc approach：

A1，A2：Execute SQL statements to retrieve data from T1 and T2.

A3：Convert ids values in A2’s records to sequences one by one, and match them with T1’s records to get sequences of names, which then will be converted to records separated by the comma. The result is like this:

Of course, the above approach is based on the assumption that values of id are consecutive integers. If they are not, then the last step could be rephrased as follows:

  A3：>A1.primary(id)

A4：=A2.(ids.array().(A1.find(~).name ).string@d())

The SQL approach uses subquery to generate distinct group numbers, and then solves the problem with relatively complex JOIN. The following shows how it is coded in MySQL:

  select group_concat(t1.name) names from(

     select @row:=@row+1 as gid,ids from t2,(SELECT @row :=0 ) r

) t2 left join t1 on find_in_set(t1.id,t2.ids) group by t2.gid

But note that this SQL approach can only apply in the situation where there are no duplicate values for ids in T2. Otherwise only one of the duplicates is allowed to be kept. For instance, the transformed result of “2,1,2,3” is “a,b,c” instead of “b,a,b,c”. By comparison, esProc syntax is more intuitive, easier to understand and can be used more widely.

esProc can be called by reporting tools as well as the JAVA program. The way is much like the one a database is called. Through JDBC interface provided by esProc, result can be returned to the JAVA main program in the form of ResultSet. See related documents for detailed method. 

esProc Codes Column Shuffling

In database table REF_VALUES, ID column acts as the primary key and ORIGINAL_VALUE is used as the original column, whose values are to be shuffled and updated to the empty SHUFFLED_VALUE column. Below is a selection from the table:

SQL approach:

Databases differ in their solution to the problem. Here Oracle is used as the example:

create or replace procedure shuffle as

TYPE My_CurType IS REF CURSOR; 

CUR_1 My_CurType;

V_id NUMBER(10,0);

V_result varchar2(20);

v_sql varchar2(2000);

begin

  OPEN CUR_1 FOR  select t1.id,t2.result from (

        select rownum rn,id,ORIGINAL_VALUE from REF_VALUES order by ORIGINAL_VALUE) t1

    join(

        select rownum rn,result from(

            select ORIGINAL_VALUE result from REF_VALUES order by dbms_random.value()))t2

    on t2.rn=t1.rn;

  LOOP

    fetch CUR_1 INTO V_id,V_result ; 

       EXIT WHEN CUR_1%NOTFOUND; 

    v_sql:='update REF_VALUES set SHUFFLED_VALUE='''||V_result||''' where id='||V_id;

    EXECUTE immediate v_sql;  

  END LOOP;

  CLOSE  CUR_1;

end shuffle;

You need to write multilayered nested subqueries to get the shuffling result (as shown by the first half of the above code), then you need cursors (or temporary tables) and the stored procedure to insert it to the target table. The code will be rather tedious.

The esProc approach allows us to dispense with the complex nested subqueries and applies to various types of databases. The code is as follows:

A1: Execute the SQL statement to get data from ID column and ORIGINAL_VALUE column.

A2: Shuffle the values in ORIGINAL_VALUE column.

A3: Join A1’s ID column with A2’s ORIGINAL_VALUE column to create a two-dimensional table, as shown below:

A4：Update REF_VALUES table with A3’s table. @u option means only generating the UPDATE statement. The updated REF_VALUES table is as follows: