select statement & loop

[Former Member]

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.

[Former Member]

Hi

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

[Former Member]

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........

[jayanthi_jayaraman]

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.

[Former Member]

hi,

It is the no 1 performence killer.

Instead use FOR ALL ENTRIES .

regards,

Guru

• mark if helpful

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

[Former Member]

To avoid select statement between loop and end loop<br>

use loop and read statements.<br>

e.g:- <br>

1.<b> Select all data related to your internal table in one hit by for all entries.</b> <br>

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

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

select matnr maktx into table ITAB_desc<br>

from MAKT for all entries in ITAB_MATNO<br>

where matnr = ITAB-MATNR.<br>

it will fetch the decription for all entries. <br>

2. 1.<b> Use read inside loop and modify internal table</b><br>

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

<b>loop at ITAB_MATNO.</b><br>

*read and modify the plant name in an internal table<br>

<b> read table ITAB with<br>

key matnr = ITAB-matnr. </b><br>

ITAB_MATNO-MAKTX = ITAB-MAKTX.<br>

modify ITAB_MATNO.<br>

clear ITAB_MATNO.<br>

clear ITAB.<br>

endloop.<br>