topic Re: Delete unique records from internal table query in Application Development and Automation Discussions

Delete unique records from internal table query

Former Member — Tue, 25 Sep 2012 05:24:12 GMT

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.

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

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

Re: Delete unique records from internal table query

Former Member — Tue, 25 Sep 2012 06:55:47 GMT

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

Re: Delete unique records from internal table query

former_member214878 — Tue, 25 Sep 2012 07:34:51 GMT

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.

Re: Delete unique records from internal table query

Former Member — Tue, 25 Sep 2012 09:52:32 GMT

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

Re: Delete unique records from internal table query

Former Member — Tue, 25 Sep 2012 10:01:03 GMT

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,

Re: Delete unique records from internal table query

Amarpreet — Tue, 25 Sep 2012 10:14:36 GMT

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

Re: Delete unique records from internal table query

Former Member — Tue, 25 Sep 2012 10:51:31 GMT

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,

Re: Delete unique records from internal table query

amy_king — Tue, 25 Sep 2012 13:07:45 GMT

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

Re: Delete unique records from internal table query

Former Member — Wed, 26 Sep 2012 07:49:10 GMT

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.

Re: Delete unique records from internal table query

Former Member — Wed, 26 Sep 2012 11:48:24 GMT

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.

Re: Delete unique records from internal table query

Former Member — Wed, 26 Sep 2012 11:55:22 GMT

Use like this

sort itab by f1 .

itab1[] = itab[].

if sy-subrc = 0 .

delete itab1 where f1 = wa-f1.

endif .

sort itab1 by f1 .

Re: Delete unique records from internal table query

Former Member — Thu, 27 Sep 2012 16:46:02 GMT

Thanks Amy for the reply...

It solved my issue