...

Add Missing Sequence Numbers to One Table according to The Other and Combine Two Tables

 
order-based calculation(16)

Problem description & analysis

There is a OLDTABLE in a database. Its RANK column contains continuous numbers beginning from 1, as shown below:

ITEM

RANK

o1

1

o2

2

o3

3

o4

4

o5

5

There is also a NEWTABLE in the database. Its RANK table contains discontinuous numbers that included in OLDTABLE’s RANK, as shown below:

ITEM

RANK

n1

3

n2

5

We are trying to insert records of NEWTABLE into OLDTABLE according to the orders of RANK in the two tables and the record after the newly-inserted record has the rank RANK+1, as shown below:

ITEM

RANK

o1

1

o2

2

n1

3

o3

4

n2

5

o4

6

o5

7

Solution

Write the following script p1.dfx in esProc:

A

1

=connect("demo")

2

=A1.query("SELECT * FROM OLDTABLE ORDER BY RANK")

3

=A1.query("SELECT * FROM NEWTABLE ORDER BY RANK")

4

>A1.close()

5

>A3.run(A2.insert(RANK,A3.ITEM,A3.RANK))

6

>A2.run(RANK=#)

7

return A2

Explanation:

A1   Connect to the database named demo.

A2  Perform SQL and return the query result as a table sequence.

A3  Perform SQL and return the query result as a table sequence.

A4  Close database connection.

A5  Loop through A3 to insert the current record into A2 at the position corresponding to the current RANK value of A3.

A6  Assign corresponding sequence numbers to RANK in A2.

A7  Return result of A2.

Read How to Call an SPL SCript in Java to learn about the method of integrating the SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/64141189/combine-tables-based-on-fixed-rank


order-based calculation(16)
 
 •  211 View  •