Select query inside loop

[Former Member]

Hello Experts,

I have a requirement where I have to loop an internal table (it_final) and compare the data of it_final to my Ztable(zebeln_table) by using a select query.

If the data are already present inside the ztable(zebeln_table), I should skip it. Or else, I will do some operations with that data.

I developed select query inside the loop which is not accepted by coding standards.

Hope you can understand the requirement.

Thanks in advance.

[former_member656115]

Hi Dinesh

You can use for all entries to get data before loop and then can use read inside loop.

For example,
if it_final is not initial
Select field1 field2... fieldn
from Zebeln_table
into table it_zebelntbl
for all entries in it_final
where field1 = it_final-field1.
endif.
Loop at it_final into wa_final.
read table it_zebelntbl with key..
*If data exists
if sy-subrc = 0.
*If data not exists
else.
endif
Endloop. 

Thanks,

Guna

[mohit_dev]

Hi Dinesh,

Yes, it is not accepted to have select query inside loop.

1. Just fetch the required fields from your Z-Table for all entries in your internal table before the loop.

2. Then, inside loop read the data of your Z-table fetched above and proceed as you want.

Regards,
Mohit Sharma

[Former Member]

Hello Mohit Sharma,

My actual requirement is when I pass multiple values into the loop for operation, I get the values in a separate internal table and gets the current value from z-table using select query which is inside the loop and I modify my z-table with the values I passed inside the loop and fetches the present values from z-table in next iteration of loop till the loop ends.

Hope, you understood my logic.

Thanks in advance.

[mohit_dev]

Hi Dinesh, Yes I understood your requirement.

Once all the data is fetched by you before the loop you can READ that data just "like" select single inside your loop statement and do whatever possible you want to do. This way you wont hit the database again and again inside your LOOP statement.

If you still have some doubts, post your code and we will help you with the same.

Regards,
Mohit Sharma

[Former Member]

Here is my code,

LOOP AT it_final INTO wa_final WHERE sel = 'X'.
      IF wa_final-ebeln IS NOT INITIAL.
            wa_ebeln-anln1 = wa_final-anln1.
            wa_ebeln-anln2 = wa_final-anln2.
            wa_ebeln-menge = wa_final-menge.
            wa_ebeln-ebeln = wa_final-ebeln.
            wa_ebeln-mblnr = wa_final-mblnr.
            wa_ebeln-meins = wa_final-meins.
            lv_total = lv_total + wa_ebeln-menge.
            APPEND wa_ebeln TO it_ebeln.
            CLEAR : wa_ebeln.
      ELSEIF wa_final-belnr IS NOT INITIAL.
            wa_belnr-anln1 = wa_final-anln1.
            wa_belnr-anln2 = wa_final-anln2.
            wa_belnr-menge = wa_final-menge1.
            wa_belnr-meins = wa_final-meins.
            wa_belnr-belnr = wa_final-belnr.
            lv_total1 = lv_total1 + wa_belnr-menge.
            APPEND wa_belnr TO it_belnr.
            CLEAR : wa_belnr.
      ENDIF.
      wa_menge-anln1 = wa_final-anln1.
      wa_menge-anln2 = wa_final-anln2.
      wa_menge-ebeln = wa_final-ebeln.
      wa_menge-meins = wa_final-meins.
      wa_menge-mblnr = wa_final-mblnr.
      wa_menge-belnr = wa_final-belnr.
      wa_menge-menge = lv_total + lv_total1 .
      lv_final = wa_menge-menge.
      wa_menge-final = lv_final.
      APPEND wa_menge TO it_menge.
*      Method is called to get menge from Zfi_quantity table
      CALL METHOD zcl_asset_barcode=>asset_menge
        EXPORTING
          i_mandt  = sy-mandt
          it_menge = it_menge
        IMPORTING
          it_check = it_check
          .
      CLEAR : wa_menge.
      LOOP AT it_check INTO wa_check.
          lv_check = lv_check + wa_check-final.
          lv_prev_qty = wa_check-menge.
      ENDLOOP.
*      Method is called from HANA for data validation of ebeln, mblnr and belnr.
      CALL METHOD zcl_asset_barcode=>asset_valid
        EXPORTING
          i_mandt  = sy-mandt
          it_menge = it_menge
        IMPORTING
          it_valid = it_valid
          .
      IF it_valid IS NOT INITIAL.                          
        MESSAGE 'Barcodes are already generted'(021) TYPE 'I'.
      ELSE.
            LOOP AT it_menge INTO wa_menge.               
                  wa_zfi-anln1 = wa_menge-anln1.
                  wa_zfi-anln2 = wa_menge-anln2.
                  wa_zfi-belnr = wa_menge-belnr.
                  wa_zfi-ebeln = wa_menge-ebeln.
                  wa_zfi-mblnr = wa_menge-mblnr.
                  wa_zfi-menge = wa_menge-menge.
                  wa_zfi-meins = wa_menge-meins.
                  wa_zfi-final = wa_menge-final.
                  APPEND wa_zfi TO it_zfi.
                  MODIFY zfi_quantity FROM wa_zfi.
                  COMMIT WORK.
                  lv_refresh = wa_zfi-final.
            ENDLOOP.
            IF lv_check EQ 0.
                lv_count = 1.
                lv_print = lv_refresh.
            ELSE.
                lv_count = lv_check + 1.
                lv_print = lv_refresh.
            ENDIF.
                DO lv_print TIMES.
                   wa_barcode-anln1 = wa_final-anln1.
                   wa_barcode-anln2 = wa_final-anln2.
                   CONCATENATE wa_final-bukrs wa_final-anln1 wa_final-anln2 lv_count INTO wa_barcode-barcode.      
                   APPEND wa_barcode TO it_barcode.
                   CLEAR wa_barcode.
                   lv_count = lv_count + 1.
                ENDDO.
                PERFORM barcode_print.
        ENDIF.
          CLEAR : lv_total, lv_total1, lv_count, lv_count1, lv_refresh, lv_check, lv_final.
          CLEAR : it_check[], it_menge[], it_ebeln[], it_belnr[], it_valid[].
      ENDLOOP.

[mohit_dev]

But Dinesh, where is your select query you are referring from the start of this question?

[former_member1716]

Hello 7dinesh,

Your approach is not recommended because it will affect the performance badly. Instead recommend you to follow the below Steps:

1) Write a single Select Query on your custom table for all the entries in your internal table. Before writing a query using FOR ALL ENTRIES, recommend you to perform below operations as well.

a) Check if the table is not empty

b) Sort and ensure all the duplicate entries are deleted based on the Key fields.

2) Sort the internal Table which has the entries fetched above based on the key fields.

3) Inside your Loop you can use the read statement by using the using the Key Fields.

This will ensure better performance and satisfies the coding standards.

Regards!

[michael_piesche]

It is not recommended, for the same reason, the SELECT.....END OF SELECT. should not be used:

• For each iteration, a connection and a request to the database has to be made through the network, resulting in bad performance for the entire "loop"
• Selecting only necessary data in one single select statement always outperforms doing the same but splitted over many select statements

After selecting large datasets from DB, the thing you need to pay attention to, is whether you should access those internal tables by primary or secondary keys, if you only need certain records in single iterations (otherwise you will loose your performance gain from above to some degree again).

[Nawanandana]

please share your code

[Former Member]

Hello experts,

Here is my code. I have to eliminate select query

LOOP AT it_final INTO wa_final WHERE sel = 'X'.
      IF wa_final-ebeln IS NOT INITIAL.
            wa_ebeln-anln1 = wa_final-anln1.
            wa_ebeln-anln2 = wa_final-anln2.
            wa_ebeln-menge = wa_final-menge.
            wa_ebeln-ebeln = wa_final-ebeln.
            wa_ebeln-mblnr = wa_final-mblnr.
            wa_ebeln-meins = wa_final-meins.
            lv_total = lv_total + wa_ebeln-menge.
            APPEND wa_ebeln TO it_ebeln.
            CLEAR : wa_ebeln.
      ELSEIF wa_final-belnr IS NOT INITIAL.
            wa_belnr-anln1 = wa_final-anln1.
            wa_belnr-anln2 = wa_final-anln2.
            wa_belnr-menge = wa_final-menge1.
            wa_belnr-meins = wa_final-meins.
            wa_belnr-belnr = wa_final-belnr.
            lv_total1 = lv_total1 + wa_belnr-menge.
            APPEND wa_belnr TO it_belnr.
            CLEAR : wa_belnr.
      ENDIF.
      wa_menge-anln1 = wa_final-anln1.
      wa_menge-anln2 = wa_final-anln2.
      wa_menge-ebeln = wa_final-ebeln.
      wa_menge-meins = wa_final-meins.
      wa_menge-mblnr = wa_final-mblnr.
      wa_menge-belnr = wa_final-belnr.
      wa_menge-menge = lv_total + lv_total1 .
      lv_final = wa_menge-menge.
      wa_menge-final = lv_final.
      APPEND wa_menge TO it_menge.
*      Method is called to get menge from Zfi_quantity table
      CALL METHOD zcl_asset_barcode=>asset_menge
        EXPORTING
          i_mandt  = sy-mandt
          it_menge = it_menge
        IMPORTING
          it_check = it_check
          .
      CLEAR : wa_menge.
      LOOP AT it_check INTO wa_check.
          lv_check = lv_check + wa_check-final.
          lv_prev_qty = wa_check-menge.
      ENDLOOP.
*      Method is called from HANA for data validation of ebeln, mblnr and belnr.
      CALL METHOD zcl_asset_barcode=>asset_valid
        EXPORTING
          i_mandt  = sy-mandt
          it_menge = it_menge
        IMPORTING
          it_valid = it_valid
          .
      IF it_valid IS NOT INITIAL.                          
        MESSAGE 'Barcodes are already generted'(021) TYPE 'I'.
      ELSE.
            LOOP AT it_menge INTO wa_menge.               
                  wa_zfi-anln1 = wa_menge-anln1.
                  wa_zfi-anln2 = wa_menge-anln2.
                  wa_zfi-belnr = wa_menge-belnr.
                  wa_zfi-ebeln = wa_menge-ebeln.
                  wa_zfi-mblnr = wa_menge-mblnr.
                  wa_zfi-menge = wa_menge-menge.
                  wa_zfi-meins = wa_menge-meins.
                  wa_zfi-final = wa_menge-final.
                  APPEND wa_zfi TO it_zfi.
                  MODIFY zfi_quantity FROM wa_zfi.
                  COMMIT WORK.
                  lv_refresh = wa_zfi-final.
            ENDLOOP.
            IF lv_check EQ 0.
                lv_count = 1.
                lv_print = lv_refresh.
            ELSE.
                lv_count = lv_check + 1.
                lv_print = lv_refresh.
            ENDIF.
                DO lv_print TIMES.
                   wa_barcode-anln1 = wa_final-anln1.
                   wa_barcode-anln2 = wa_final-anln2.
                   CONCATENATE wa_final-bukrs wa_final-anln1 wa_final-anln2 lv_count INTO wa_barcode-barcode.      
                   APPEND wa_barcode TO it_barcode.
                   CLEAR wa_barcode.
                   lv_count = lv_count + 1.
                ENDDO.
                PERFORM barcode_print.
        ENDIF.
          CLEAR : lv_total, lv_total1, lv_count, lv_count1, lv_refresh, lv_check, lv_final.
          CLEAR : it_check[], it_menge[], it_ebeln[], it_belnr[], it_valid[].
      ENDLOOP.