topic Re: Delete Statement does not work as expected in Application Development and Automation Discussions

Delete Statement does not work as expected

former_member295881 — Mon, 25 Jun 2012 18:14:03 GMT

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.

Re: Delete Statement does not work as expected

Former Member — Mon, 25 Jun 2012 18:34:18 GMT

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

Re: Delete Statement does not work as expected

former_member295881 — Mon, 25 Jun 2012 18:45:47 GMT

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.

Re: Delete Statement does not work as expected

ShyamPindiproli — Mon, 25 Jun 2012 18:53:31 GMT

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

Re: Delete Statement does not work as expected

former_member295881 — Mon, 25 Jun 2012 19:08:39 GMT

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.

Re: Delete Statement does not work as expected

Former Member — Mon, 25 Jun 2012 19:53:59 GMT

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

Re: Delete Statement does not work as expected

ShyamPindiproli — Mon, 25 Jun 2012 19:54:09 GMT

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

Re: Delete Statement does not work as expected

former_member295881 — Mon, 25 Jun 2012 20:16:38 GMT

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

Re: Delete Statement does not work as expected

Clemenss — Mon, 25 Jun 2012 20:23:31 GMT

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

Re: Delete Statement does not work as expected

former_member295881 — Mon, 25 Jun 2012 20:28:12 GMT

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