Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Delete Statement does not work as expected

former_member295881
Contributor
0 Kudos
1,215

Hi Experts,

I’m trying to improve the performance of an interface. This requires a check on KNVV internal table based it’s KUNNR value. I need to delete all KUNNR in KNA1 internal table if they are not found in KNVV table. I’ve accomplished this task if I use loop and work area. But performance is my concern and that’s why I tried to use DELETE statement (as shown below) to remove all customers from KNA1 which are not found in KNVV. However, the following piece of code deletes everything. Can somebody point out what’s wrong with the following DELETE statement OR I must have to use loop and workarea to achieve my goal.

delete p_li_data_kna1
         where kunnr eq p_li_data_knvv-kunnr.
           if sy-subrc eq 0.
             sort p_li_data_kna1 by kunnr.
           endif.

For comparison of two table I've attached a screen shot. (In this example I want to delete '0000010668' from LI_DATA_KNA1)

Many thanks in advance.

1 ACCEPTED SOLUTION

ShyamPindiproli
Active Participant
0 Kudos
749

I would suggest making use of an extra field MARK and set it to 'X' for all the rows to be deleted rather  than deleting in a loop.

And once out of the loop Delete all entries where MARK = 'X'.

Regards,

Shyam

9 REPLIES 9

Former Member
0 Kudos
749

Hi,

You are expecting it to work like 'FOR ALL ENTRIES' for a select. This is an incorrect usage. 'p_li_data_knvv-kunnr' in your statement represents the header of internal table p_li_data_knvv. Instead loop at your  KNA1 internal table. READ KNVV internal table (Sorted and then binary search addition preferred) and then delete.

Regards,

Shravan

0 Kudos
749

Hi Shravan,

Many thanks for your input. I understand my mistake now. But as per your suggestion READ KNVV will only work for a single record. What if I've 50 row to delete in KNA1 which do not exit in KNVV.

ShyamPindiproli
Active Participant
0 Kudos
750

I would suggest making use of an extra field MARK and set it to 'X' for all the rows to be deleted rather  than deleting in a loop.

And once out of the loop Delete all entries where MARK = 'X'.

Regards,

Shyam

0 Kudos
749

Many thanks guys, I've been able to write the following code to accomplish my task.

form F_CHECK_PI_KNA1  tables   p_li_data_kna1 type tt_data_kna1
                                p_li_data_knvv type tt_data_knvv.

data: wa_cust_knvv type t_cust_knvv,
       wa_cust_kna1 type t_cust_knb1.

   if lines( p_li_data_kna1 ) gt 0 and lines( p_li_data_knvv ) gt 0.

     loop at p_li_data_knvv into wa_cust_knvv.

       read table p_li_data_kna1 into wa_cust_kna1
         with key kunnr = wa_cust_knvv-kunnr
           BINARY SEARCH.

         if sy-subrc ne 0.
           delete p_li_data_kna1
             where kunnr eq wa_cust_kna1-kunnr.
         endif.
     endloop.

       if sy-subrc eq 0.
         sort p_li_data_kna1 by kunnr.
       endif.
   endif.
endform.

I can not find a way where I can avoid using Loop (which is my concern). Any suggestions how I can improve the above piece of code.

0 Kudos
749

Hi,

"sort p_li_data_kna1 by kunnr" should be before the read statement. Read ... Binary search without sort will give wrong results. Put it just before the 'loop at p_li_data_knvv'.  

Regards,

Shravan

0 Kudos
749

You can avoid multiple DELETE statements, by getting it out of the loop 

 

form F_CHECK_PI_KNA1  tables   p_li_data_kna1 type tt_data_kna1
                                p_li_data_knvv type tt_data_knvv.

data: wa_cust_knvv type t_cust_knvv,
       wa_cust_kna1 type t_cust_knb1.

   if lines( p_li_data_kna1 ) gt 0 and lines( p_li_data_knvv ) gt 0.

     loop at p_li_data_knvv into wa_cust_knvv.
    
  CLEAR wa_cust_knvv.
       read table p_li_data_kna1 into wa_cust_kna1
         with key kunnr = wa_cust_knvv-kunnr
           BINARY SEARCH.

         if sy-subrc ne 0.

            wa_cust_knvv-MARK = 'X'.
            MODIFY  p_li_data_knvv FROM wa_cust_knvv TRANSPORTING MARK.

         endif.
     endloop.

       if sy-subrc eq 0.
         sort p_li_data_kna1 by kunnr.
       endif.
   endif.
endform.

DELETE p_li_data_knvv WHERE MARK EQ 'X'.

Regards,

Shyam

0 Kudos
749

Many thanks guys for such a great help. Your suggestions have solved my issue and more importantly I've learned something new today

Clemenss
Active Contributor
0 Kudos
749

Hi zero,

first thing to do: No LOOP INTO anymore. Replace with LOOP ASSIGNING <field-symbol>.

Use sorted or hashed tables to boost performance, try this:

FIELD-SMBOLS:

  <data_knvv> like line of p_li_data_knvv,

  <data_kna1> like line of p_li_data_kna1.

DATA:

  lt_kunnr TYPE SORTED TABLE OF knvv-kunnr

    WITH UNIQUE KEY table_line. 

LOOP AT p_li_data_knvv ASSIGNING <data_knvv>.

  INSERT <data_knvv>-kunnr INTO TABLE lt_kunnr.

ENDLOOP. 

LOOP AT p_li_data_kna1 ASSIGNING <data_kna1>.

  READ TABLE lt_kunnr TRANSPORTING NO FIELDS

    WITH TABLE KEY table_line = <data_kna1>-kunnr.

  CHECK SY-SUBRC <> 0.

  DELETE p_li_data_kna1.

ENDLOOP. 

Still, it depends on the number of rows to be kept/deleted. You may also put the KUNNR in a sorted range and then DELETE p_li_data_kna1 WHERE NOT KUNNR IN range.

Looks like

FIELD-SMBOLS:

  <data_knvv> like line of p_li_data_knvv,

  <data_kna1> like line of p_li_data_kna1,

DATA:

  lt_r_kunnr   TYPE RANGE OF knvv-kunnr,

  ls_r_kunnr   LIKE LINE OF lt_r_kunnr,

  lt_r_kunnr_s LIKE SORTED TABLE OF ls_r_kunnr

    WITH UNIQUE KEY table_line.

ls_r_kunnr = 'IEQ'. 

LOOP AT p_li_data_knvv ASSIGNING <data_knvv>.

  ls_r_kunnr = <data_knvv>-kunnr.

  INSERT ls_r_kunnr INTO TABLE lt_r_kunnr_s .

ENDLOOP. 

DELETE p_li_data_kna1 WHERE NOT kunnr IN lt_r_kunnr_s .

But RANGES are evaluated comparably slow if they have more lines.

Try what works for you. Both is faster than any LOOP INTO.

Regards,

Clemens

0 Kudos
749

Many thanks for excellent examples Clemens. Certainly I will try them