DELETE ADJACENT DUPLICATES does not delete duplicates

Question

I have an internal table that is filled with 108 entries. From 9 to 9 entries it's repeating the entries and I wanted to delete those duplicates. Since they're exactly the same I used the delete adjacent duplicates from itab comparing all fields. Also tried without comparing all fields. No success.

If it helps, my table has 9 fields: bukrs, hkont, gjahr, belnr, budat, waers, shkzg, wrbtr, dmbtr and dmbe2. They're from BSIS and they're in this order too. This is the DO loop in which is the SELECTenter code here. I've putted the DELETE outside of the DO loop.

The two first SELECT's are working fine and belong to the previous code that existed.

DO 12 TIMES.
         lv_aux = lv_aux + 1.
         lv_tamanho = STRLEN( lv_aux ).
         IF lv_tamanho = 1.
           CONCATENATE '0' lv_aux INTO lv_aux.
         ENDIF.
         CONCATENATE p_gjahr lv_aux '01' INTO z_v_first_day.

         PERFORM get_last_day_of_month USING z_v_first_day
                                       CHANGING lv_last_day.

         " some other code irrelevant to the issue

         SELECT bukrs hkont gjahr belnr budat waers shkzg dmbtr wrbtr dmbe2 FROM bsis
           APPENDING CORRESPONDING FIELDS OF TABLE gt_bancbsis
           WHERE hkont = '0051100001'
           AND bukrs EQ p_bukrs
           AND budat <= lv_last_day.

         " some other code irrelevant to the issue
ENDDO.

DELETE ADJACENT DUPLICATES FROM gt_bancbsis COMPARING ALL FIELDS.

This is a picture of the internal table gt_bancbsis in the dubugger.

Answer 1

The word ADJACENT in the statement DELETE ADJACENT DUPLICATES is there for a very good reason: It states that only duplicate lines that are next to each other are removed. This is also stated in the online keyword documentation:

Rows are considered to be duplicate if the content of neighboring row is the same in the components examined. In the case of multiple duplicate rows following one another, all the rows (except for the first) are deleted.

This means: If your table contains the values

 A B
 C D
 A B
 C D

it contains duplicate values, but since these are not adjacent, DELETE ADJACENT DUPLICATES will not remove them, no matter what you specify.

The SELECT statement on the other hand does not guarantee a specific order when returning the datasets selected unless you tell it to ORDER BY one or more columns. The rows are just returned in any order, and if the DELETE ADJACENT DUPLICATES statement works, it's pure coincidence. It might even work on one system, stop working on another and remove only half of the duplicates on a third system. So, cardinal rule:

Make sure that your internal table is sorted by the fields you want to be checked for duplicates BEFORE deleting the duplicates.

For better scalability, you should use the SORT statement instead of having the database sort the rows with ORDER BY. So you could use either

SORT gt_bancbsis.
DELETE ADJACENT DUPLICATES FROM gt_bancbsis.

or, if you only want to check for certain fields,

SORT gt_bancbsis BY foo bar baz.
DELETE ADJACENT DUPLICATES FROM gt_bancbsis COMPARING foo bar baz.

Answer 2

In BSIS table, you are using 4 jey fields( bukrs, hkont, gjahr, belnr ). Use these field only for sorting.

1. Sort the internal table first.

   SORT ITAB WITH KEY ITAB-FIELDS.
   

2. Then COMPARING fields.

    DELETE ADJACENT DUPLICATES FROM ITAB
   

   It will work fine.