...

Get the First Missing Item for the Sequence of Should-have-been Continuous Sequence Numbers

 
order-based calculation(16)

Problem description & analysis

We have a table BOOK_CAPTER in the database. The data is as follows:

BOOK_ID

CAPTER_INTERNAL_NUMBER

1

1

1

2

1

5

2

1

2

2

2

3

3

7

3

9

We are trying to find the first missing item for the sequence that should have contained continuous sequence numbers (CAPTER_INTERNAL_NUMBER) in each group of BOOK_ID. Below is the desired result:

BOOK_ID

NUM

1

3

2

4

3

1

Solution

Write the following script p1.dfx in esProc:

A

1

=connect("demo")

2

=A1.query@x("SELECT * FROM BOOK_CAPTER ORDER BY   BOOK_ID,CAPTER_INTERNAL_NUMBER")

3

=create(BOOK_ID,NUM)

4

>A2.run(if(A3.select@1(ID==A2.ID),,if(ID!=ID[-1] &&   N!=1,A3.insert(0,A2.ID,1),if(ID==ID[-1] &&   N>N[-1]+1,A3.insert(0,A2.ID,N[-1]+1),if(ID!=ID[+1],A3.insert(0,A2.ID,N+1)))))   )

Explanation:

A1   Connect to the database named demo.

A2  Return the query result as a table sequence and close database connection when code is executed.

A3  Create the result empty table sequence made up of fields BOOK_ID and NUM.

A4  Loop through A2, during which if the current ID exists in the result table sequence (A3), go on to check ID in the next record; and if the current ID is not equal to the previous ID and the current N isn’t 1, append record [the current ID,1] to A3; and if the current ID is not equal to the previous ID and the current N and the N in the previous record are not continuous, append record [the current ID, the previous record’s N+1] to A3; and if the current ID is not equal to the next ID, append record [the current ID, the current N+1] to A3.

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/64144082/find-first-available-value-that-doesnt-exist


order-based calculation(16)
 
 •  214 View  •