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

select statement inside loop

Former Member — Fri, 09 Nov 2012 18:10:11 GMT

Hi,

I am having multiple select statements inside loop. I need to write select query's outside loop and use read statement inside loop. Will the read statement fetch all the fields? I have no clear idea of writing the read table inside a loop. Can anyone provide me the sample code for writing multiple select outside loop?

Thanks,

Venkata.

Re: select statement inside loop

amy_king — Fri, 09 Nov 2012 18:38:16 GMT

Hi Venkata,

The idea is to essentially "pre-read" the data you need before entering the loop so as to avoid repeated select statements inside the loop. Imagine how many select statements would result if the following itab had 1000 records (answer is 1000 of course)...

LOOP AT itab ASSIGNING <itab>.
    SELECT SINGLE field
        FROM db_table
        INTO <itab>-field2.
        WHERE db_table_field = <itab>-field1.  
ENDLOOP.

A more performant approach would be to first read db_table into an internal table and then read from the internal table while inside the loop...

SELECT *
    FROM db_table
    INTO TABLE lt_db_table
    WHERE ...  
LOOP AT itab ASSIGNING <itab>.
    READ TABLE lt_db_table ASSIGNING <db_table>
            WITH KEY db_table_field = <itab>-field1.
    IF sy-subrc IS INITIAL.
        <itab>-field2 = <db_table>-field.
    ENDIF.
ENDLOOP.

Cheers,

Amy

Re: select statement inside loop

Former Member — Fri, 09 Nov 2012 18:47:48 GMT

Hello,

In addition to what Amy has suggested I would recommend to sort the internal tables after your selects and use the read table with binary search in the loop statements. It would increase the ABAP processing speed a little bit.

Also you could have ALL the select statements outside the loop, these could 2, 3 or 5 or 10 and then inside the loop you would have the read statements. This way your total report/interface time would be fast. And you need to make sure that your select selects or gets all the fields you need so that your read will have that information.

best regards,

swanand

Re: select statement inside loop

Former Member — Sat, 10 Nov 2012 05:15:00 GMT

Hi venkat,

Select Query inside a loop is not preferable as it hits the table every time the loop runs.

So if there are 10k entries within the loop " THEN THE SELECT QUERY INSIDE THE LOOP ALSO RUNS 10K TIMES AND HITS THE DATA BASE TABLE ALSO 10K TIMES WHICH LEADS TO MORE EXECUTION TIME AND PERFORMANCE ISSUE".

So instead of dis fetch the data before the loop and use the read table inside the loop to get the data.

I have attached code in file pls see once ..

if it_bseg is not initial.

select chect

laufd

lifnr

vblnr

zaldt

rwbtr

pridt

from payr

into table it_payr

for all entries in it_bseg

where lifnr = it_bseg-lifnr

and vblnr = it_bseg-belnr..

endif.

loop at it_bseg into wa_bseg.

read table it_payr into wa_payr with key lifnr = wa_bseg-lifnr.

wa_final-v_cheque = wa_payr-chect.

wa_final-bal = wa_final-dmbtr - wa_final-pswbt.

wa_final-pridt = wa_payr-pridt.

append wa_final to it_final.

endif.

endloop.

endform.

Regards,

chandu.

Re: select statement inside loop

Former Member — Sat, 10 Nov 2012 18:04:37 GMT

Hi Amy King,

loop at itab3.

select single text1 into itab3-text1 from t052u where zterm = itab3-zterm and

spras = 'en'.

if itab3-vkbur = ' '.

select single vkbur into itab3-vkbur from knvv where kunnr = itab3-kunnr.

endif.

select single vkgrp into itab3-vkgrp from knvv where kunnr = itab3-kunnr.

modify itab3.

endloop.

I have written the above code as:

select * from t052u into table itab3 where ZTERM = itab3-ZTERM AND

SPRAS = 'EN'.

if itab3-vkbur = ' '.

select single vkbur from knvv into table itab3 where KUNNR = itab3-KUNNR.

endif.

select single vkgrp from knvv into table itab3 where KUNNR = itab3-KUNNR.

sort itab3 by vkbur.

loop at itab3.

read table itab3 into wa_itab3 with key knvv-kunnr = itab3-kunnr binary search.

if sy-subrc = 0.

modify itab3.

endif.

endloop.

Is this the correct one? Please guide me.

Thanks,

Venkata.

Re: select statement inside loop

amy_king — Sun, 11 Nov 2012 15:21:26 GMT

Hi Venkata,

In your code snippet above, you're using "itab3" for every table. I'm not sure if you mean this literally or not. As long as you're not really using "itab3" for every table, I think you understand the approach, though you may need to modify this statement...

loop at itab3.

read table itab3 into wa_itab3 with key knvv-kunnr = itab3-kunnr binary search.

if sy-subrc = 0.

modify itab3 from wa_itab3 transporting field1 field2 field3.

endif.

endloop.

Just in case, see the example below to understand the approach...

Before the code below is executed, table itab_people looks like this...

person_id	last_name	first_name	street	city	state
12345
23456
34567

* Pre-read people's first and last names into internal table itab_names
SELECT *
    FROM db_table_names
    INTO TABLE itab_names
    WHERE ...
* Pre-read people's address information into internal table itab_address
SELECT *
    FROM db_table_address
    INTO TABLE itab_address
    WHERE ...
* Loop through the table of people, filling in their name and address details
LOOP AT itab_people ASSIGNING <person>.
*   Fill in first and last name 
    READ TABLE itab_names ASSIGNING <name>
                WITH KEY person_id = <person>-person_id.
    IF sy-subrc IS INITIAL.
        <person>-last_name = <name>-last_name.
        <person>-first_name = <name>-first_name.
    ENDIF>
*   Fill in address details
    READ TABLE itab_address ASSIGNING <address>
                WITH KEY person_id = <person>-person_id.
    IF sy-subrc IS INITIAL.
        <person>-street = <address>-street.
        <person>-city = <address>-city.
        <person>-state = <address>-state
    ENDIF>
ENDLOOP.

After the code has executed, table itab_people looks like this...

person_id	last_name	first_name	street	city	state
12345	Bourne	Michael	Main	New York	NY
23456	Chance	Sarah	Pleasant	Chicago	IL
34567	Sewell	Daniel	Vernon	Los Angeles	CA

Cheers,

Amy