topic How to avoid select query in loop in Application Development and Automation Discussions

How to avoid select query in loop

Former Member — Mon, 26 Oct 2009 10:54:35 GMT

Hi All,

I have to select entries from table iclpay looping it_dfkkop and delete the entries in it_dfkkop_tmp which are not present in iclpay.

For this If use below code its working fine, but there is performance issue

CLEAR: it_iclpay[],it_iclpay.

LOOP AT it_dfkkop_tmp.

SELECT claim documentref paycat status FROM iclpay

APPENDING CORRESPONDING FIELDS OF TABLE it_iclpay

WHERE

active = c_active AND

claim = it_dfkkop_tmp-claim AND

documentref = it_dfkkop_tmp-opbel.

IF sy-subrc NE 0.

DELETE it_dfkkop_tmp.

ENDIF.

ENDLOOP.

If I write the code like below its not working

SELECT claim documentref paycat status FROM iclpay

INTO TABLE it_iclpay

FOR ALL ENTRIES IN it_dfkkop_tmp WHERE

active = c_active AND

claim = it_dfkkop_tmp-claim AND

documentref = it_dfkkop_tmp-opbel.

Can anyone help on this.

Re: How to avoid select query in loop

Former Member — Mon, 26 Oct 2009 11:07:24 GMT

What do you mean by not working? The purpose of using FOR ALL ENTRIES is to avoid select query within loop. Are you getting any error or dump with your second select query?

Re: How to avoid select query in loop

Former Member — Mon, 26 Oct 2009 12:04:22 GMT

It did not dump but did not get final(required) output.

I got the solution now.

Thanks a lot for responding.

I have written the below code

SELECT claim

documentref

paycat

status

FROM

iclpay

APPENDING CORRESPONDING FIELDS OF TABLE it_iclpay

FOR ALL ENTRIES IN it_dfkkop_tmp WHERE

active = c_active AND

claim = it_dfkkop_tmp-claim AND

documentref = it_dfkkop_tmp-opbel.

SORT it_dfkkop_tmp BY claim opbel.

LOOP AT it_dfkkop_tmp.

READ TABLE it_iclpay INTO wa_iclpay WITH KEY

claim = it_dfkkop_tmp-claim

documentref = it_dfkkop_tmp-opbel.

IF sy-subrc NE 0.

DELETE it_dfkkop_tmp.

ENDIF.

ENDLOOP.

Re: How to avoid select query in loop

Former Member — Mon, 26 Oct 2009 12:05:03 GMT

Answered

Re: How to avoid select query in loop

Former Member — Mon, 26 Oct 2009 12:08:24 GMT

if it selects nothing, then there probably nothing to delete.

Your FAE looks fine to me:

What you need is:

define itab1 as SORTED TABLE ...


if NOT ( itab IS INITIAL).
  SELECT 
       INTO TABLE itab1
       ...
       FOR ALL ENTRIES itab0
       ... 
ENDIF.

if NOT ( itab1 IS INITIAL).
  LOOP AT itab0 INTO wa0.
     READ TABLE itab1 WITH KEY ...
     IF ( sy/subrc ne 0 ).
       APPEND wa TO itab2.
      ENDIF.
   ENDLOOP.
   DELETE dbtab FROM TABLE itab2.
  ENDIF.
ELSE
   DELETE dbtab FROM TABLE itab0.
ENDIF.

details are not hard to figure out!

Re: How to avoid select query in loop

Former Member — Mon, 26 Oct 2009 12:09:31 GMT

Answered

only half