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

Problem description & analysis

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

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:

Solution

Write the following script p1.dfx in esProc:

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