11-09-2012 6:10 PM
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.
11-09-2012 6:38 PM
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
11-09-2012 6:38 PM
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
11-09-2012 6:47 PM
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
11-10-2012 5:15 AM
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.
11-10-2012 6:04 PM
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.
11-11-2012 3:21 PM
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