How to avoid the loop inside loop .

[former_member220286]

Dear All,

I have a requirement that I need to avoid the below inside two loops of the

KONV table .How we can replace these loops .I am working on optimization of a zreport .So I need to reduce these inside loops of the KONV table .Please suggest some workaround or ideas .

LOOP AT it_vbrp INTO wa_vbrp .
  READ TABLE it_vbrk INTO wa_vbrk WITH KEY vbeln = wa_vbrp-vbeln
        BINARY SEARCH.
  LOOP AT it_konv INTO wa_konv
      WHERE knumv = wa_vbrk-knumv AND kposn = wa_vbrp-posnr
        AND ( kschl = 'ZF00' OR kschl = 'ZF01' OR kschl = 'ZF02'
           OR kschl = 'ZF03' OR kschl = 'ZF04'
           OR kschl = 'ZF05' OR kschl = 'ZF06' 
           OR kschl = 'ZF07' OR kschl = 'ZFCD'
           OR kschl = 'ZFI2' OR kschl = 'ZFIM'
           OR kschl = 'ZIFI' ).

    IF wa_vbrk-waerk = 'USD'.
      wa_konv-kwert = wa_konv-kwert / 100.
    ENDIF.
    tafkwert = tafkwert + wa_konv-kwert.
    wa_final-fkwert = wa_final-fkwert + wa_konv-kwert.
    wa_final-netwr = wa_final-netwr + wa_konv-kwert.
  ENDLOOP.

  LOOP AT it_konv INTO wa_konv WHERE knumv = wa_vbrk-knumv
                                 AND kposn = wa_vbrp-posnr
                                 AND ( kschl = 'ZF01' OR kschl = 'ZF05').
    IF wa_vbrk-waerk = 'USD'.
      wa_konv-kwert = wa_konv-kwert / 100.
    ENDIF.
    wa_konv-kwert = wa_konv-kwert.

    tlfkwert = tlfkwert + wa_konv-kwert."LESS FREIGHT
    wa_final-lfkwert = wa_konv-kwert."LESS FREIGHT
    wa_final-netwr = wa_final-netwr + wa_konv-kwert.
  ENDLOOP.
ENDLOOP.

Please suggest some ideas or workaround .

Regards

Deep

[Former Member]

Dear Deep,

Both "inside loop" do similar things:

• checks the contition types (kschl)
• divides condition value (kwert) by 100 in case of USD currency
• and finally calculates different key figures (fkwert, netwr, etc..)

My advices are the following:

• push down the "condition types checks" from the WHERE expression to two IF expressions inside the main LOOP
• place the currency conversion code block at the begining of the main LOOP (it was duplicated in your code)

Of course you can further optimize my changes as well (for example: the calculation of the "wa_final-netwr", it is also duplicated in the code) if you want.

Hope this helps!

Warm regards,

Alex

LOOP AT it_vbrp INTO wa_vbrp .
  READ TABLE it_vbrk INTO wa_vbrk WITH KEY vbeln = wa_vbrp-vbeln BINARY SEARCH.
  
  LOOP AT it_konv INTO wa_konv
    WHERE knumv = wa_vbrk-knumv
      AND kposn = wa_vbrp-posnr.
    IF wa_vbrk-waerk = 'USD'.
      wa_konv-kwert = wa_konv-kwert / 100.
    ENDIF.

    IF ( kschl = 'ZF00' OR kschl = 'ZF02' OR kschl = 'ZF03' OR 
         kschl = 'ZF04' OR kschl = 'ZF06' OR kschl = 'ZF07' OR 
         kschl = 'ZFCD' OR kschl = 'ZFI2' OR kschl = 'ZFIM' OR 
         kschl = 'ZIFI' ).
      tafkwert        = tafkwert + wa_konv-kwert.
      wa_final-fkwert = wa_final-fkwert + wa_konv-kwert.
      wa_final-netwr  = wa_final-netwr + wa_konv-kwert.
    ELSEIF ( kschl = 'ZF01' OR kschl = 'ZF05' ).
      tlfkwert         = tlfkwert + wa_konv-kwert. "LESS FREIGHT
      tafkwert         = tafkwert + wa_konv-kwert.
      wa_final-fkwert  = wa_final-fkwert + wa_konv-kwert.
      wa_final-lfkwert = wa_konv-kwert. "LESS FREIGHT
      wa_final-netwr   = wa_final-netwr + wa_konv-kwert.
    ENDIF.
  ENDLOOP.
ENDLOOP.

[former_member220286]

Dear Alex Gönczy ,

I have done the same as told by you . I have done the following :-

• push down the "condition types checks" from the WHERE expression to two IF expressions inside the main LOOP.
• place the currency conversion code block at the begining of the main LOOP (it was duplicated in your code).

LOOP AT it_vbrp INTO wa_vbrp .

READTABLE it_vbrk INTO wa_vbrk WITHKEY vbeln = wa_vbrp-vbeln BINARYSEARCH.

LOOP AT it_konv INTO wa_konv
    WHERE knumv = wa_vbrk-knumv
      AND kposn = wa_vbrp-posnr.IF wa_vbrk-waerk ='USD'.
      wa_konv-kwert = wa_konv-kwert /100.ENDIF.

IF( kschl ='ZF00'OR kschl ='ZF02'OR kschl ='ZF03'OR 
         kschl ='ZF04'OR kschl ='ZF06'OR kschl ='ZF07'OR 
         kschl ='ZFCD'OR kschl ='ZFI2'OR kschl ='ZFIM'OR 
         kschl ='ZIFI').
      tafkwert        = tafkwert + wa_konv-kwert.
      wa_final-fkwert = wa_final-fkwert + wa_konv-kwert.
      wa_final-netwr  = wa_final-netwr + wa_konv-kwert.ELSEIF( kschl ='ZF01'OR kschl ='ZF05').
      tlfkwert         = tlfkwert + wa_konv-kwert."LESS FREIGHT
      tafkwert         = tafkwert + wa_konv-kwert.
      wa_final-fkwert  = wa_final-fkwert + wa_konv-kwert.
      wa_final-lfkwert = wa_konv-kwert."LESS FREIGHT
      wa_final-netwr   = wa_final-netwr + wa_konv-kwert.

ENDIF.

ENDLOOP.

ENDLOOP.

I am asking can the loop on IT_KONV table can be avoided .

Can the below three queries can be combined into one .

As some one suggested : - " Try changing the data selection to use a JOIN select involving these three tables, so that you have all relevant data in one single internal table " .

Please guide me how to make the below three queries in to one .

-----------------------------

First Query

-------------------------------

SELECT VBELN
FKART
WAERK
VKORG
VTWEG
KNUMV
FKDAT
NETWR
KUNAG
VTWEG
XBLNR FROM VBRK INTO TABLE IT_VBRK

WHERE VBELN IN S_VBELN “ invoice no.

AND FKART IN S_FKART " Invoice type

AND FKART NOT IN ('ZG2','ZL2','ZF5','ZRE','F8')

AND VKORG = P_VKORG " sales organisation

AND VTWEG IN P_VTWEG " distribution channel

AND FKDAT IN S_DATE " date of invoice

AND KUNAG IN S_KUNAG AND

KUNRG IN S_KUNRG "Ship To Party

AND SFAKN EQ SPACE " cancelled invoices have refrence doc no. here

AND FKSTO NE 'X' " to exclude cancelled invoices

AND REGIO IN S_BLAND AND MRNKZ NE 'X'.

IF SY-SUBRC NE 0.

MESSAGE 'No Data Found' TYPE 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

------------------------------------

second query

-------------------------------------

SELECT VBELN
POSNR
FKIMG
VRKME
VGBEL
AUBEL
MATNR
ARKTX
CHARG
WERKS
KONDM
VKAUSFROM VBRPINTO TABLE IT_VBRPFOR ALL ENTRIES IN IT_VBRKWHERE VBELN = IT_VBRK-VBELN AND MATNR IN S_MATAND SPART IN P_SPART AND CHARG IN S_CHARG AND WERKS IN P_WERKS AND KONDM IN S_MATNR .

IF SY-SUBRC NE 0.

MESSAGE 'No Data Found' TYPE 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

-----------------------------------

Third Query

-----------------------------------

SELECT KNUMV
KPOSN
STUNR
ZAEHK
KSCHL
KBETR
KWERT
KINAK FROM KONV INTO CORRESPONDING FIELDS OF TABLE IT_KONV FOR ALL ENTRIES IN IT_VBRK WHERE KNUMV = IT_VBRK-KNUMV.

DELETE IT_KONV WHERE KINAK = 'X'.

delete it_konv where kinak = 'Y'.
SORT IT_KONV BY KNUMV KPOSN.

How can the data be selected through a join for all these three tables : VBRK , VBRP and KONV table .

JOIN select as some suggested me . How the above three queries can be combined .

Also I am doing some calculations inside the below loop of IT_KONV table as you already know .

How can they be done if all three tables are combined into the one table .In this way the loop will be avoided and hence the read statement will do the work .

Regards

Deep

[ThomasZloch]

I assume that data is selected from tables VBRK, VBRP and KONV prior to the code part we are seeing.

Try changing the data selection to use a JOIN select involving these three tables, so that you have all relevant data in one single internal table.

Then the loop inside loop issue goes away, you would only need to loop one table and do additional calculations as required.

[matt]

Also research on how to use HASHED tables with COLLECT: They're super efficient for this purpose.

[former_member220286]

Dear Thomas ,

Thanks for your valuable reply .

As you are suggesting : - " Try changing the data selection to use a JOIN select involving these three tables, so that you have all relevant data in one single internal table " .

Please guide me how to make the below three queries in to one .

SELECT VBELN
FKART
WAERK
VKORG
VTWEG
KNUMV
FKDAT
NETWR
KUNAG
VTWEG
XBLNR FROM VBRK INTO TABLE IT_VBRK WHERE VBELN IN S_VBELN “ invoice no.

AND FKART IN S_FKART " Invoice type

AND FKART NOT IN ('ZG2','ZL2','ZF5','ZRE','F8')

AND VKORG = P_VKORG " sales organisation

AND VTWEG IN P_VTWEG " distribution channel

AND FKDAT IN S_DATE " date of invoice

AND KUNAG IN S_KUNAG AND

KUNRG IN S_KUNRG "Ship To Party

AND SFAKN EQ SPACE " cancelled invoices have refrence doc no. here

AND FKSTO NE 'X' " to exclude cancelled invoices

AND REGIO IN S_BLAND AND MRNKZ NE 'X'.

IF SY-SUBRC NE 0.

MESSAGE 'No Data Found' TYPE 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

SELECT VBELN
POSNR
FKIMG
VRKME
VGBEL
AUBEL
MATNR
ARKTX
CHARG
WERKS
KONDM
VKAUSFROM VBRPINTO TABLE IT_VBRPFOR ALL ENTRIES IN IT_VBRKWHERE VBELN = IT_VBRK-VBELN AND MATNR IN S_MATAND SPART IN P_SPART AND CHARG IN S_CHARG AND WERKS IN P_WERKS AND KONDM IN S_MATNR .

IF SY-SUBRC NE 0.

MESSAGE 'No Data Found' TYPE 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

SELECT KNUMV
KPOSN
STUNR
ZAEHK
KSCHL
KBETR
KWERT
KINAK FROM KONV INTO CORRESPONDING FIELDS OF TABLE IT_KONV FOR ALL ENTRIES IN IT_VBRK WHERE KNUMV = IT_VBRK-KNUMV.

DELETE IT_KONV WHERE KINAK = 'X'.

delete it_konv where kinak = 'Y'.
SORT IT_KONV BY KNUMV KPOSN.

How can the data be selected through a join for all these three tables : VBRK , VBRP and KONV table .

JOIN select as you are suggesting me . How the above three queries can be combined .

Also I am doing some calculations inside the below loop . How can they be done if all three tables are combined : -

LOOP AT it_vbrp INTO wa_vbrp .  

    READTABLE it_vbrk INTO wa_vbrk WITH  KEY vbeln = wa_vbrp-vbeln BINARYSEARCH.

LOOP AT it_konv INTO wa_konv WHERE knumv = wa_vbrk-knumv AND kposn = wa_vbrp-posnr.

IF wa_vbrk-waerk ='USD'.

      wa_konv-kwert = wa_konv-kwert /100.ENDIF.IF( kschl ='ZF00'OR kschl ='ZF02'OR kschl ='ZF03'OR 
         kschl ='ZF04'OR kschl ='ZF06'OR kschl ='ZF07'OR 
         kschl ='ZFCD'OR kschl ='ZFI2'OR kschl ='ZFIM'OR 
         kschl ='ZIFI').
      tafkwert        = tafkwert + wa_konv-kwert.
      wa_final-fkwert = wa_final-fkwert + wa_konv-kwert.
      wa_final-netwr  = wa_final-netwr + wa_konv-kwert.ELSEIF( kschl ='ZF01'OR kschl ='ZF05').
      tlfkwert         = tlfkwert + wa_konv-kwert."LESS FREIGHT
      tafkwert         = tafkwert + wa_konv-kwert.
      wa_final-fkwert  = wa_final-fkwert + wa_konv-kwert.
      wa_final-lfkwert = wa_konv-kwert."LESS FREIGHT
      wa_final-netwr   = wa_final-netwr + wa_konv-kwert.

ENDIF.

ENDLOOP.

ENDLOOP.

Please suggest some ideas or workaround .

Regards

Deep

[matt]

Please do not extend an existing question to ask a new question. If you have a new question, create a new question. As you are now asking a different question, I assume you've got the answer to this one, so I'll close it.

[former_member220286]

Dear Matthew Billingham ,

It is related question . Please try to understand.If you keep on doing this thing then I am unable to get a resolution for the same .I have updated the reply to Thomas Zloch .

Regards

Deep

[matt]

Understand that this is how moderation on this site works. I've told you what you need to do. Others seems to be able to follow migrator guidance.

Open a new question. You can link to the old one if you want to retain context.

This really is for your own benefit. You'll get more and better help if you keep things clear, rather than rambling on with different requirements in different replies in the same question

I am not changing established procedures for one person who doesn't want to.

I'm locking this question now, and closing the comments.

[omer_sakar]

Hi,

In addition to the above comments, you can use parallel cursor :

Reference link : https://wiki.scn.sap.com/wiki/display/Snippets/ABAP+Code+for+Parallel+Cursor+-+Loop+Processing

[matt]

If you use HASHED and SORTED tables appropriately, you won't need to use old fashioned techniques like parallel cursor.

HASHED and SORTED tables have only been around for 18 years, so I'm not surprised that people carry on using BINARY SEARCH and suggesting parallel cursor...

[matt]

Please in future make your code nicely formatted. If people can read it, they'll be able to help you better. Use the CODE button in the editor. Hint: if you're copying from the abap editor, when you paste, right click and use "Paste as plain text". Be sure to tidy up to keep things readable.

I've done it for you this time.

[RaymondGiuseppi]

If you define it_konv as a sorted table with key knumv and kposn, you souldn't get some performance problem from the second loop at it will be optimized on the index. Also use a single secondary loop, just use some CASE wa_konv-kschl to fill final table.

[DoanManhQuynh]

Hi Deep.

I think you should let the loop on KONV to outside since it will have less record than VBRP. Beside, you should inner join VBRP and VBRK to get knumv and posnr. Condition type ZF01 and ZF05 has special logic so I put that logic into same loop too. something like:

LOOP AT IT_KONV INTO WA_KONV
 WHERE ( KSCHL = 'ZF00' OR KSCHL = 'ZF01' OR KSCHL = 'ZF02'
 OR KSCHL = 'ZF03' OR KSCHL = 'ZF04'
 OR KSCHL = 'ZF05' OR KSCHL = 'ZF06'
 OR KSCHL = 'ZF07' OR KSCHL = 'ZFCD'
 OR KSCHL = 'ZFI2' OR KSCHL = 'ZFIM'
 OR KSCHL = 'ZIFI' ).

 CHECK line_exists( IT_VBRK[ KNUMV = WA_KONV-KNUMV
 KPOSN = WA_KONV-POSNR ] ).

 IF WA_VBRK-WAERK = 'USD'.
 WA_KONV-KWERT = WA_KONV-KWERT / 100.
 ENDIF.
 TAFKWERT = TAFKWERT + WA_KONV-KWERT.
 WA_FINAL-FKWERT = WA_FINAL-FKWERT + WA_KONV-KWERT.
 WA_FINAL-NETWR = WA_FINAL-NETWR + WA_KONV-KWERT.

 IF KSCHL = 'ZF01' OR KSCHL = 'ZF05'.
 IF WA_VBRK-WAERK = 'USD'.
 WA_KONV-KWERT = WA_KONV-KWERT / 100.
 ENDIF.
 WA_KONV-KWERT = WA_KONV-KWERT.

 TLFKWERT = TLFKWERT + WA_KONV-KWERT."LESS FREIGHT
 WA_FINAL-LFKWERT = WA_KONV-KWERT."LESS FREIGHT
 WA_FINAL-NETWR = WA_FINAL-NETWR + WA_KONV-KWERT.
 ENDIF.
ENDLOOP.

[former_member220286]

Dear Quynh Doan Manh ,

Can the loop on IT_KONV can be avoided .

Regards

Deep

[former_member539238]

Hi,

If you know the pricing conditions that you need then you can avoid it by looping through the invoice item table and readint it_konv for required data based on pricing condition.

[DoanManhQuynh]

You could SUM in SQL first (its depend on your logic) then you read vbrk to get line item post in USD and convert. It should be better because the calculation is pushed to DB. something like:

SELECT KNUMV AS KNUMV,
 KPOSN AS KPOSN,
 SUM( KWERT ) AS KWERT
 FROM KONV
 WHERE KSCHL = 'ZF00' OR KSCHL = 'ZF01' OR KSCHL = 'ZF02'
 OR KSCHL = 'ZF03' OR KSCHL = 'ZF04'
 OR KSCHL = 'ZF05' OR KSCHL = 'ZF06'
 OR KSCHL = 'ZF07' OR KSCHL = 'ZFCD'
 OR KSCHL = 'ZFI2' OR KSCHL = 'ZFIM'
 OR KSCHL = 'ZIFI'
 GROUP BY KNUMV, KPOSN
 INTO TABLE @DATA(LT_KONV).<br>
 LOOP AT LT_KONV INTO WA_KONV.
 CHECK line_exists( IT_VBRK[ KNUMV = WA_KONV-KNUMV KPOSN = WA_KONV-POSNR ] ).
 ...
 ENDLOOP.

you are summing data so you cant avoid loop, can only make it more effective. if you dont loop KONV then you have to loop on vbrk anw.

[roberto_forti]

Hi Deep,

Join is the best way but try below code and let me know the results.

TYPES KSCHL_X TYPE RANGE OF KSCHA. "Condition Type 
TYPES KSCHL_Y TYPE RANGE OF KSCHA. "Condition Type 

*ZF00 / ZF01 / ZF02 ETC.
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZF00' high = '') ). 
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZF01' high = '') ).
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZF02' high = '') ).
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZF03' high = '') ).   
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZF04' high = '') ).
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZF05' high = '') ).  
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZF06' high = '') ). 
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZF07' high = '') ). 
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZFCD' high = '') ).
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZFI2' high = '') ).  
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZFIM' high = '') ). 
DATA(KSCHL_X) = VALUE KSCHL_X( ( sign = 'I' option = 'EQ' low = 'ZIFI' high = '') ). 

* ZF01 / ZF05
DATA(KSCHL_Y) = VALUE KSCHL_Y( ( sign = 'I' option = 'EQ' low = 'ZF01' high = '') ).
DATA(KSCHL_Y) = VALUE KSCHL_Y( ( sign = 'I' option = 'EQ' low = 'ZF05' high = '') ).

DATA: lv_tabix TYPE sytabix,
      lv_index TYPE sytabix.

SORT: it_vbrp[] BY VBELN POSNR,
      it_konv[] BY KNUMV KPOSN,
      KSCHL_X[], 
      KSCHL_Y[].

*Header
LOOP AT it_vbrk[] INTO wa_vbrk.

*Item by primary key vbeln 
  READ TABLE it_vbrp[] TRANSPORTING NO FIELDS BINARY SEARCH
    WITH KEY vbeln = wa_vbrk-vbeln.

  IF ( SY-SUBRC EQ 0 ).
    lv_tabix = sy-tabix.  

*Item by index
    LOOP AT it_vbrp[] INTO wa_vbrp FROM lv_tabix.

       IF ( wa_vbrp-vbeln NE wa_vbrk-vbeln ).
         EXIT.
       ELSE.
         IF ( wa_vbrk-waerk EQ 'USD' ).
           wa_konv-kwert = wa_konv-kwert / 100.
         ENDIF.

         READ TABLE it_konv[] TRANSPORTING NO FIELDS BINARY SEARCH
           WITH KEY knumv = wa_vbrk-knumv
                    kposn = wa_vbrp-posnr.

         IF ( SY-SUBRC EQ 0 ).
             lv_index = sy-tabix.

            LOOP AT it_konv[] INTO wa_konv FROM lv_index.

               IF ( wa_konv-knumv NE wa_vbrk-knumv OR
                    wa_konv-kposn NE wa_vbrp-posnr ).
                     EXIT.
               ELSE.
* ZF00 / ZF01 / ZF02 etc.
                  IF ( wa_konv-kschl IN KSCHL_X[] ).
                     tafkwert = tafkwert + wa_konv-kwert.
                     wa_final-fkwert = wa_final-fkwert + wa_konv-kwert.
                     wa_final-netwr = wa_final-netwr + wa_konv-kwert.
                  ENDIF. 
* ZF01 / ZF05
                  IF ( wa_konv-kschl IN KSCHL_Y[] ).
                     wa_konv-kwert = wa_konv-kwert.

                     tlfkwert = tlfkwert + wa_konv-kwert."LESS FREIGHT
                     wa_final-lfkwert = wa_konv-kwert."LESS FREIGHT
                     wa_final-netwr = wa_final-netwr + wa_konv-kwert.
                  ENDIF.
               ENDIF.

              CLEAR: lv_index.
            ENDLOOP.

         ENDIF.
       ENDIF.

       CLEAR: wa_vbrp.
    ENDLOOP.

  ENDIF.

  CLEAR: wa_vbrk,
         wa_konv,
         lv_tabix.

ENDLOOP.

[Patrick_vN]

How about a loop on IT_KONV and grouping by KNUMV and POSNR? Completed with a LOOP AT GROUP-, a CASE- and possibly a READ statement (or two) it should do the trick.

Of course if you'd have the possibility to JOIN some of the tables in the corresponding SQL, it would only become easier.

[former_member220286]

Thank You so much sir .