Delete unique records from internal table query

[Former Member]

Hi Experts,

I am reposting my question just want to explain the scenario:

I want to delete only those duplicates from my internal table where status is E009 or E008.

For example in the below scenario the logic should give me 2 records and delete only 1

record i.e objnr = 123 and status E009.

Here is my internal table:

OBJNR   STATUS

123        E009

123        E011

789       E009

My Logic is as below but this is not working:

IT_TEMP[] = ITAB[] ->Move data in temp table

loop at itab.

loop at it_temp into wa where objnr = itab-objnr.

Add 1 to count.

append wa to lt_duplicated ->Get duplicate entries

check count = 2.

loop at duplicates into wa_duplicates where stat = E009 or stat = E008 ->check which duplicate entry has status

endloop.

wa_duplicates-flag = 'X'.

modify itab from wa_duplicates. ->mark that record with flag as X

endloop.

endloop.

Delete ITAB where flag = X. "Remove this record from table

Guys can someone please suggest a better logic or help me modifying this one?

[Former Member]

Hi Experts,

I am reposting my question just want to explain the scenario:

I want to delete only those duplicates from my internal table where status is E009 or E008.

For example in the below scenario the logic should give me 2 records and delete only 1

record i.e objnr = 123 and status E009.

Here is my internal table:

OBJNR   STATUS

123        E009

123        E011

789       E009

My Logic is as below but this is not working:

IT_TEMP[] = ITAB[] ->Move data in temp table

loop at itab.

loop at it_temp into wa where objnr = itab-objnr.

Add 1 to count.

append wa to lt_duplicated ->Get duplicate entries

check count = 2.

loop at duplicates into wa_duplicates where stat = E009 or stat = E008 ->check which duplicate entry has status

endloop.

wa_duplicates-flag = 'X'.

modify itab from wa_duplicates. ->mark that record with flag as X

endloop.

endloop.

Delete ITAB where flag = X. "Remove this record from table

Guys can someone please suggest a better logic or help me modifying this one?

[Former Member]

Hi,

Just use the below statement it will work.

Your Case

OBJNR   STATUS

123        E009

123        E011

789       E009

Use Below Lines :

SORT ITAB BY STATUS.

DELETE ITAB WHERE STATUS = 'E009' OR STATUS = 'E008'.

Result :

As per Unique Record Output.

OBJNR  STATUS

123         E011.

Regards,

S.Chandrakumar

[former_member214878]

Hi,

No need to loop and set the flag for duplicate and make it more complicated.

Chandrakumar has suggested a good way of doing it, however I am not sure if u have only "E009" and "E008" for comparison.

If you have more fields for comparison in future and your data is more in the internal table WHERE clause with all the fields may reduce your performance.

So better you try as below -

I will suggest you to create a range table for Comparison entries (Sign, option, low, high) and then use only one statement -

<CODE>

DELETE ITAB WHERE STATUS IN LT_COMPARE.

</CODE>

create LT_COMPARE as a range table and have entries "E009" and "E008" and if more required in that.

Hope this will save your complicated logic building time and energy

Thanks and Regards,

Ravindra Sonar.

[Former Member]

it was so clearly mentioned that the result of above scenario should be 2 records.

I want to delete only those duplicates from my internal table where status is E009 or E008.

For example in the below scenario the logic should give me 2 records and delete only 1

record i.e objnr = 123 and status E009.

Here is my internal table:

OBJNR   STATUS

123        E009

123        E011

789       E009

[Former Member]

Hi,

Sort your table first( Ascending/Descending( and use DELETE ADJACENT Duplicates syntax. You dont need any loop as such.

SORT ITAB by objnr descending status ascending.

Delete adjacent duplicates from ITAB comparing status.

Hope it helps.

Regards,

R

[Amarpreet]

Hi,

everything has already been said about the delete statement ,

just wanted to add the bit about using the COMPAIRING addition.

When you SORT an internal table and use the DELETE ADJACENT statement it will check all the primary key components during the check ,

So incase you want it to only check similar entries in a specific row eg. the OBJNR in your example :

use :

sort itab .

delete adjacent itab compairing OBJNR .

hope this helps ..

good luck

[Former Member]

Hello,

yes sort first the internal table and use delete adjacent duplicates from the internal table comparing field which you find to be be key field which will distinguish the entries.

Regards,

[amy_king]

Hi B M,

If I understand your requirement, you want to delete only duplicates having a status of E008 or E009. So if you have the following duplicates:

#  OBJNR  STATUS
1  123    E008
2  123    E009
3  123    E011

You want to delete records #1 and #2 and keep record #3. If I have understood your requirement correctly, try the following approach:

* We're only interested in deleting records where status is
* E008 or E009 so only loop at those records
   LOOP AT itab ASSIGNING <itab>
                WHERE status = 'E008'
                OR    status = 'E009'.
 
     lv_itab_index = sy-tabix.
 
*   Check if the same objnr exists elsewhere in the table
     LOOP AT itab TRANSPORTING NO FIELDS
                  FROM lv_itab_index
                  WHERE objnr = <itab>-objnr.
       EXIT. " exit the loop if we found a match
     ENDLOOP.
 
*   If the above loop finds a match, sy-subrc will be 0. If not,
*   sy-subrc will be 4. So, if we find a match, delete the current 
*   itab record.
     IF sy-subrc IS INITIAL.
       DELETE itab.
     ENDIF.
 
   ENDLOOP. " <itab>

Cheers,
Amy

[Former Member]

Thanks Amy for the reply...

It solved my issue

[Former Member]

Hi BM,

Just  copy & paste this sample code and test, it suits ur requirement .

TYPES:BEGIN OF TY_TYPES,
       OBJNR TYPE OBJNR,
       STATUS(6) TYPE C,
       END OF TY_TYPES. 

DATA:ITAB TYPE TABLE OF TY_TYPES," to hold records with status other than E009
        ITAB1 TYPE TABLE OF TY_TYPES,"to hold all records
        ITAB2 TYPE TABLE OF TY_TYPES, " to hold records with status E009
         ITAB3 TYPE TABLE OF TY_TYPES, "final table
         WA2 TYPE TY_TYPES,
         WA TYPE TY_TYPES,
         WA1 TYPE TY_TYPES.


       WA-OBJNR =  '123'.
       WA-STATUS = 'E009'.
       APPEND WA TO ITAB.

       WA-OBJNR =  '1232'.
       WA-STATUS = 'E011'.
       APPEND WA TO ITAB.

       WA-OBJNR =  '1237'.
       WA-STATUS = 'E012'.
       APPEND WA TO ITAB.


ITAB1 = ITAB.
*********Appending records with E009 status to itab2*********
LOOP AT ITAB INTO WA.
   READ TABLE ITAB1 INTO WA1  INDEX SY-TABIX.
   IF WA1-STATUS = 'E009'.
      APPEND WA1 TO ITAB2.
     ENDIF.
  ENDLOOP.

******Deleting the records with status E009 from itab*******
DELETE ITAB WHERE STATUS = 'E009'.


**checkin if the record exists in both tables
* if it exists then an deleting that record from status table
* finally am appending records to final internal table
  LOOP AT ITAB1 INTO WA1.
READ TABLE ITAB2 INTO WA2 WITH KEY OBJNR = WA1-OBJNR.
     IF SY-SUBRC = 0.
READ TABLE ITAB INTO WA WITH KEY OBJNR = WA1-OBJNR.
     IF SY-SUBRC = 0.
DELETE ITAB2 WHERE OBJNR = WA2-OBJNR.
      ENDIF.
      ENDIF.
  ENDLOOP.

APPEND LINES OF ITAB TO ITAB3.
APPEND LINES OF ITAB2 TO ITAB3.

         BREAK-POINT.

[Former Member]

first sort the inetrnal table by status and then use delete adjacent duplicates from inernal table.

SORT itab BY status.

DELETE ADJACENT DUPLICATES FROM itab comparing status.

PFB screenshot which shows the contents of internal table before applying these statements

PFB the screenshot which shows  the contents of the internal table after using this 2 line code in the program.

[Former Member]

Use like this

sort itab by f1 .

itab1[]  = itab[].

if sy-subrc = 0 .

delete itab1 where f1 = wa-f1.

endif .

sort itab1 by f1 .