ABAP – How to find a gap in range of numbers

Posted on by

SAPIn this article I’ll share a piece of code I use to find a gap (the first minimal number that does not exist) between the given MIX and MAX numbers. It is not an easy task like in other SQL languages so you have to apply a little bit of coding in ABAP.

In other SQL languages, (e.g. MySQL or Oracle) you can do something like the following SQL command:

SELECT A-sequence + 1
  FROM my_table AS A
  LEFT OUTER JOIN my_table AS B 
    ON B-sequence = A-sequence + 1
  WHERE B-sequence = NULL.

This ‘+ 1’ is currently NOT possible in ABAP Open SQL, therefore you are not able to use such commands and you have to implement your own search algorithm.

In the following piece of code I’ll show how to find the first unused material number in the MARA table.

Please keep in mind that the presented approach (selecting all data to internal table and looping over it) has MUCH better performance than finding the same result using SELECT-ENDSELECT !!!

DATA:
  lt_matnr    TYPE TABLE OF matnr,
  lv_next_gap TYPE matnr,
  lv_min      TYPE matnr VALUE 7000000,
  lv_max      TYPE matnr VALUE 7999999.

* Select all used numbers
SELECT matnr
  INTO TABLE lt_matnr
  FROM mara
  WHERE matnr BETWEEN lv_min AND lv_max
  ORDER BY matnr ASCENDING.

* Set counter to the minimal possible value
lv_next_gap = lv_min.
TRY.
* While we are in the range && number is used in the DB table
    WHILE lv_next_gap          <= lv_max AND
          lt_matnr[ sy-index ] =  lv_next_gap.

*     Increment our number which will be checked in the next loop
*     Note: This increment can be substituted with more complex
*           data manipulation in case the material number is not 
*           just a numeric value.
      ADD 1 TO lv_next_gap.     

    ENDWHILE.   

* We stepped out of ITAB bounds
  CATCH cx_sy_itab_line_not_found. 
ENDTRY. 

IF lv_next_gap > lv_max.
  WRITE 'No gap available'.
ELSE.
  WRITE lv_next_gap.
ENDIF.

Leave a Reply