topic Re: select statement & loop in Application Development and Automation Discussions

select statement & loop

Former Member — Mon, 11 Dec 2006 06:37:31 GMT

hi gurus,

i need to fine tune one report.......in which one slect statement was used in between loop and end loop ....

how to avoid it........

can u give any ex: code for this.

Re: select statement & loop

Former Member — Mon, 11 Dec 2006 06:40:21 GMT

Any possible to inner join with the existing select query? else use for all entries or specific selection. Fine tune by giving the exact condition, it will giving the result immediately.

Give ue code, then we can find the right solution

Re: select statement & loop

Former Member — Mon, 11 Dec 2006 06:41:54 GMT

hi,

It is the no 1 performence killer.

Instead use FOR ALL ENTRIES .

regards,

Guru

mark if helpful

Re: select statement & loop

Former Member — Mon, 11 Dec 2006 06:42:42 GMT

Hi,

U can use for all entries in table..

for ex

Select matnr from mara into table itab_mara where matnr in s_matnr.

select * from makt into table itab_makt for all entries in itab_mara where matnr = itab_mara-matnr.

make sure that itab_ara is not initial.

sort the itab_mara table for performance.

Re: select statement & loop

Former Member — Mon, 11 Dec 2006 06:43:14 GMT

SELECT MATNR FROM MARA INTO TABLE ITAB.

LOOP AT IT_MAKTX.

  READ TABLE IT_MATNR WITH KEY MATNR = IT_MATKX-MATNR.
   IF SY-SUBRC EQ 0.
  
   *u can do the processing here

    ENDIF.
ENDLOOP.

Re: select statement & loop

Former Member — Mon, 11 Dec 2006 06:47:16 GMT

thids is the code iam having.....

the itab 1 is it_parent.

data: begin of it_parent occurs 0,

lead_aufnr like caufv-lead_aufnr, " LEAD ORDER NUMBER

aufnr LIKE caufv-aufnr, " Order no

gstrp like caufv-gstrp, " Basic Start Date

werks like caufv-werks, " PLANT

auart LIKE caufv-auart, " Order Type

stlbez LIKE caufv-stlbez, " Material ( FROM CAUFV)

matnr LIKE mara-matnr, " Material

sldate like zpp_po_loc-sldate,

rsnum like caufv-rsnum,

collective type c length 1,

end of it_parent.

data: l_sldate2 like sy-datum.

loop at it_parent. "
select single sldate into l_sldate2
from zpp_po_loc
where aufnr = it_parent-lead_aufnr
and matnr = it_parent-stlbez.

endselect.

it_parent-sldate = l_sldate2.
modify it_parent transporting sldate.
clear l_sldate2.

clear it_parent.

endloop.

this is the code........

Re: select statement & loop

Former Member — Mon, 11 Dec 2006 06:48:29 GMT

Hi Dasr,

You can use SELECT... FOR ALL ENTRIES... before LOOP-ENDLOOP, let's say you save the data into internal table itab1.

Then you can replace the select statment which was used between LOOP-ENDLOOP with READ TABLE itab1 WITH KEY ... BINARY SEARCH.

I would be much faster...

Regards,

Hendy

Re: select statement & loop

Former Member — Mon, 11 Dec 2006 06:49:27 GMT

To avoid select statement between loop and end loop

use loop and read statements.

e.g:-

1. Select all data related to your internal table in one hit by for all entries.

if have list of material numbers in your interal table say itab_matno.

for all these materials you have to select the material description into another internal table say itab_desc by for all entries.

select matnr maktx into table ITAB_desc

from MAKT for all entries in ITAB_MATNO

where matnr = ITAB-MATNR.

it will fetch the decription for all entries.

2. 1. Use read inside loop and modify internal table

i will read between loop and endloop staemnt and modify my internal table

loop at ITAB_MATNO.

*read and modify the plant name in an internal table

read table ITAB with

key matnr = ITAB-matnr.

ITAB_MATNO-MAKTX = ITAB-MAKTX.

modify ITAB_MATNO.

clear ITAB_MATNO.

clear ITAB.

endloop.

Re: select statement & loop

jayanthi_jayaraman — Mon, 11 Dec 2006 06:55:16 GMT

Hi,

data : begin of itab2 occurs 0

aufnr type zpp_po_lac-aufnr,

matnr type zpp_po_lac-matnr,

sldate type zpp_po_lac-sldate,

end of itab2.

select ....into table it_parent where..

if sy-subrc eq 0.

select aufnr matnr sldate from zpp_po_lac into table itab2 for all entries in it_parent

where aufnr = it_parent-lead_aufnr

and matnr = it_parent-stlbez.

endif.

loop at it_parent.

clear itab2.

read table itab2 with key aufnr = it_parent-aufnr

matnr = it_parent-matnr.

if sy-subrc eq 0.

it_parent-sldate = l_sldate2.

modify it_parent transporting sldate where aufnr = it_parent-aufnr

matnr = it_parent-matnr.

endif.

clear it_parent.

endloop.

Kindly reward points if it helps.