![\"SAP\"](\"http:\/\/oprsteny.cz\/wp-content\/uploads\/SAP.jpg\")
In 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.<\/p>\n
In other SQL languages, (e.g. MySQL or Oracle) you can do something like the following SQL command:<\/p>\n
SELECT A-sequence + 1\r\n FROM my_table AS A\r\n LEFT OUTER JOIN my_table AS B \r\n ON B-sequence = A-sequence + 1\r\n WHERE B-sequence = NULL.<\/pre>\nThis ‘+ 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.<\/p>\n
In the following piece of code I’ll show how to find the first unused material number in the MARA table.<\/p>\n
Please keep in mind<\/strong> that the presented approach (selecting all data to internal table and looping over it) has MUCH better<\/strong> performance than finding the same result using SELECT-ENDSELECT <\/strong>!!!<\/p>\nDATA:\r\n lt_matnr TYPE TABLE OF matnr,\r\n lv_next_gap TYPE matnr,\r\n lv_min TYPE matnr VALUE 7000000,\r\n lv_max TYPE matnr VALUE 7999999.\r\n\r\n* Select all used numbers\r\nSELECT matnr\r\n INTO TABLE lt_matnr\r\n FROM mara\r\n WHERE matnr BETWEEN lv_min AND lv_max\r\n ORDER BY matnr ASCENDING.\r\n\r\n* Set counter to the minimal possible value\r\nlv_next_gap = lv_min.\r\nTRY.\r\n* While we are in the range && number is used in the DB table\r\n WHILE lv_next_gap <= lv_max AND\r\n lt_matnr[ sy-index ] = lv_next_gap.\r\n\r\n* Increment our number which will be checked in the next loop\r\n* Note: This increment can be substituted with more complex\r\n* data manipulation in case the material number is not \r\n* just a numeric value.\r\n ADD 1 TO lv_next_gap. \r\n\r\n ENDWHILE. \r\n\r\n* We stepped out of ITAB bounds\r\n CATCH cx_sy_itab_line_not_found. \r\nENDTRY. \r\n\r\nIF lv_next_gap > lv_max.\r\n WRITE 'No gap available'.\r\nELSE.\r\n WRITE lv_next_gap.\r\nENDIF.<\/pre>\n","protected":false},"excerpt":{"rendered":"In 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 … Continue reading