5 Replies Latest reply: Nov 11, 2012 4:29 PM by Amy King RSS

select statement inside loop

Venkata B
Currently Being Moderated

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
    Currently Being Moderated

    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
      Swanand Lakka
      Currently Being Moderated

      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
        Ravi Chandra
        Currently Being Moderated

        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
      Venkata B
      Currently Being Moderated

      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
        Currently Being Moderated

        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_idlast_namefirst_namestreetcitystate
        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_idlast_namefirst_namestreetcitystate
        12345BourneMichaelMainNew YorkNY
        23456ChanceSarahPleasantChicagoIL
        34567SewellDanielVernonLos AngelesCA

         

        Cheers,

        Amy

Actions