topic Re: delete rows based on condition in Application Development and Automation Discussions

delete rows based on condition

Former Member — Thu, 06 Sep 2007 12:52:31 GMT

I extract VBAK data, then I extract VBAP data with condition. Then I need to delete all orders from I_VBAK that dont exist in I_VBAP.

  SELECT vbeln auart augru lifsk faksk vkorg vtweg spart vsbed kunnr
    FROM vbak
    UP TO 10 ROWS
    INTO TABLE i_vbak
    WHERE vkorg IN s_vkorg
      AND vtweg IN s_vtweg
      AND spart IN s_spart
      AND erdat IN s_erdat
      AND vbeln IN s_vbeln
      AND kunnr IN s_kunnr
      AND lifsk IN s_lifsk
      AND faksk IN s_faksk
      AND augru IN s_augru.

  IF i_vbak[] IS NOT INITIAL.
    SELECT vbeln posnr matnr pstyv vrkme
      FROM vbap
      INTO TABLE i_vbap
      FOR ALL ENTRIES IN i_vbak
      WHERE vbeln = i_vbak-vbeln
      AND matnr IN s_matnr
      AND werks IN s_werks
      AND pstyv IN s_pstyv
      AND abgru = ' '.
  ENDIF.

Re: delete rows based on condition

Former Member — Thu, 06 Sep 2007 12:56:17 GMT

Hi Megan,

Refer this code :

Loop at I_VBAP.

Read table I_VBAK where VBELN = I_VBAP-VBELN.

If sy-subrc <> 0.

delete i_vbak.

endif.

Endloop.

Reward points if helpful.

Regards,

Hemant

Re: delete rows based on condition

Former Member — Thu, 06 Sep 2007 12:57:38 GMT

This can be done in this way. first Loop on I_vbak record and then read the I_VBAP record with the order number as the key. If record is read then keep it otherwise delete the record from the I_VBAK table.

Thanks,

Amit

Re: delete rows based on condition

Former Member — Thu, 06 Sep 2007 12:59:20 GMT

Why don't you use inner join. This is better performancewise and also simple.

SELECT a_{vbeln a}auart a_{augru a}lifsk a_{faksk a}vkorg a_{vtweg a}spart a_{vsbed a}kunnr b_{posnr b}matnr b_{pstyv b}vrkme into table itab from vbak as a inner join vbap as b where WHERE a~vkorg IN s_vkorg

AND a~vtweg IN s_vtweg

AND a~spart IN s_spart

AND a~erdat IN s_erdat

AND a~vbeln IN s_vbeln

AND a~kunnr IN s_kunnr

AND a~lifsk IN s_lifsk

AND a~faksk IN s_faksk

AND a~augru IN s_augru

AND b~matnr IN s_matnr

AND b~werks IN s_werks

AND b~pstyv IN s_pstyv

AND b~abgru = ' '.

You need to create a new internal table with rows in same sequence as mentioned in select statement above. This statement gives only records which have entries in both VBAK and VBAP tables.

Let me know if you need any more details.

Award points if you find this hint helpful.

Re: delete rows based on condition

former_member799868 — Thu, 06 Sep 2007 13:00:36 GMT

data:v_index type sy-tabix.

loop at i_vbak into s_vbak.

v_index = sy-tabix.

read table i_vbap with key vbeln = s_vbak-vbeln.

if sy-subrc <> 0.

delete i_vbak index v_index.

endif.

endloop.

Re: delete rows based on condition

Former Member — Thu, 06 Sep 2007 13:26:49 GMT

SUDHAKER gogula - Join will not give better performance. The filters are all selection criteria.

Message was edited by:

Megan Flores

Re: delete rows based on condition

Former Member — Thu, 06 Sep 2007 13:39:26 GMT

Meghan Flores - a Quick question

Question 1

Which one gives better performance from below 2 statements.

Getting data from VBAK and using FOR ALL ENTRIES getting data from VBAP.

get data using INTO TABLE using inner joins. Which one do you think is better performance wise.

You also said that filters are selection criteria. Is there limit for number of filters below which inner join is better.

Re: delete rows based on condition

Former Member — Thu, 06 Sep 2007 13:54:21 GMT

SUDHAKER gogula - I believe JOINs dont give good performance. I may or may not be passing keys in this join. If they are entered in the selection criteria then they may be passed else they wont be and this will be poor performance

Re: delete rows based on condition

Former Member — Fri, 07 Sep 2007 04:34:37 GMT

Please go though these links:

JOINS vs. FOR ALL ENTRIES - Which Performs Better?

/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better

these are the outcomes:

There are other considerations that come into play as well:

1. INNER JOINs only look at the intersection of the results that meet the WHERE clause.

2. FOR ALL ENTRIES eliminates duplicates from the results.

3. I find JOINs to be more time consuming to code. (I can never find the “~” key.)

4. When using FOR ALL ENTRIES you generally end up with at least two internal tables. This may or may not be a good thing.

5. The example I have shown uses the full primary key. Some preliminary testing I have done comparing JOINs with FOR ALL ENTRIES show that FOR ALL ENTRIES can give better performance in that case.

/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound

http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912

Finally, I think it depends on the situation to decide which one is better. Its not a compile time decision, but a run time.

Regards,

Raman.

Re: delete rows based on condition

Former Member — Fri, 07 Sep 2007 04:46:16 GMT

hi megan,

go through this code, this code will be helpfull to u.

Loop at I_VBAP.

Read table I_VBAK where VBELN = I_VBAP-VBELN.

If sy-subrc <> 0.

delete i_vbak.

endif.

Endloop.

<b>please reward points if helpfull.</b>

with regards,

radhika kolluru.