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
Top comments (1)
SPL open source address:github.com/SPLWare/esProc