Number Non-Null Values in Order within the Group — From SQL to SPL #17

Problem description & analysis:

After sorting a table in the Oracle database according to the first and second columns, the third column has non-null values.

Task: Now we need to add a calculated column RN_C1: within a group of data with the same CP, if DOPIS_C has consecutive non null values, then start from 1 and fill in the numbers in order; If DOPIS_C is null, fill in the null value in RN_C1 and renumber from the next nonnull value.

Code comparisons:

When using relative position to reference set members in SQL, it is necessary to use tedious window functions, often accompanied by nested subqueries; The logical judgment of the subsequent numbering calculation is also quite complex, with multiple layers of case when, and the code is very cumbersome. The sequence numbers need to be accumulated within a specified interval, which requires assembling these intervals into some kind of grouping, and the idea is very convoluted.

Of course, it is also possible to reference set members at relative positions in a loop and make logical judgments to avoid “detours”, but a single statement SQL cannot write a loop structure, which can be implemented using stored procedures. However, the code is still cumbersome and the framework is also complex.

SPL solution: SPL supports a complete procedural syntax that can handle complex business logic in loops, and can also conveniently reference set members in relative positions to simplify code.

A1: Load data through JDBC, sort by CP and ROK, and add a null calculated column RN_C1.

A2: Modify RN_C1 for each record: When DOPIS_C is not null, if the CP of the current record is the same as the CP of the previous record, that is, within the same group, return t+1; If cross over group, set t to 1 and return. If DOPIS_C is null, set t to null and return. Note that the initial default value of variable t is null.

esProc SPL is FREE to download and give it a try: esProc SPL FREE Download.