Avoiding Nested Loops

[Former Member]

Hi all,

I want to populate a internal table with Header and Item Level Details. BKPF is the Header Table and BSEG is the Item Details table. Can this be done without using nested LOOPS?

Consider the Logic I am using.

  SELECT bukrs
         belnr
         gjahr
         budat
         xblnr
         bktxt
         FROM   bkpf
         INTO TABLE t_bkpf
         WHERE      bukrs   IN p_bukrs " = p_bukrs
             AND    gjahr   IN p_gjahr  "= p_gjahr
             AND    monat   IN p_monat  " = p_monat
             AND    bstat    = space.


  IF sy-subrc IS INITIAL.

    SORT t_bkpf BY bukrs belnr gjahr.

    SELECT  bukrs
            belnr
            buzei
            gjahr
            hkont
            zlsch
            shkzg
            dmbtr
            FROM bseg
            INTO TABLE t_bseg
            FOR ALL ENTRIES IN t_bkpf
            WHERE     bukrs = t_bkpf-bukrs
               AND    gjahr = t_bkpf-gjahr
               AND    belnr = t_bkpf-belnr
               AND    hkont = w_hkont .

    IF sy-subrc IS INITIAL.

      SORT t_bseg BY  bukrs belnr gjahr buzei.
    ENDIF.

  ENDIF.


LOOP AT t_bkpf INTO wa_bkpf.
  LOOP AT t_bkpf INTO wa_bkpf WHERE bukrs = wa_bkpf-bukrs
                              AND   belnr = wa_bkpf-belnr
                              AND   gjahr = wa_bkpf-gjahr.
      wa_final-bukrs    =  wa_bkpf-bukrs.
      wa_final-budat    =  wa_bkpf-budat.
      wa_final-belnr    =  wa_bkpf-belnr.
      wa_final-shkzg    =  wa_bseg-shkzg.
      wa_final-dmbtr    =  wa_bseg-dmbtr.
      wa_final-xblnr    =  wa_bkpf-xblnr.
      wa_final-bktxt    =  wa_bkpf-bktxt.
      wa_final-hkont    =  wa_bseg-hkont.
      wa_final-zlsch    =  wa_bseg-zlsch.

   ENDLOOP.
ENDLOOP.

Thanks in advance.

[former_member181962]

Hi Arun,

you can use a read statement instead of the inner loop.

LOOP AT t_bseg INTO wa_bseg.

read table t_bkpf INTO wa_bkpf With key bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

endif.

ENDLOOP.

[Former Member]

Hi Ravi,Sravanthi, Kishan,

Thanks for your prompt replies.

I forgot to mention that for every record in BKPF(Header) table there are multiple entries in BSEG(Item) Table.

So LOOP and READ table cannot be used.

Regards,

Arun Sambargi

[former_member181962]

Hi Arun,

Even in that case,

the thumb rule is that you have to loop around the bigger table and read the smaller one.

loop at it_bseg into wa_bseg.

read it_bkpf into wa_bkpf with key ....

if sy-subrc = 0.

*move to final table.

endif.

endloop.

The final table will have no records that are missed out.

Regards,

Ravi

[Former Member]

SORT t_bseg BY bukrs belnr gjahr buzei.

LOOP AT t_bseg INTO wa_bseg.

read table t_bkpf INTO wa_bkpf binary search with key bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

append wa_final to itab_final.

ENDLOOP.

[former_member215188]

Hi.

This code is quite efficient. It finds the first entry via binary search, so its fast. And dont let the nested loop fool you. The loop will start with a hit (courtesy of the sy-tabix from the binary search). It will discontinue the search after last entry has been found, since everything is sorted. 'Hope this helps.

SORT t_bseg BY bukrs gjahr belnr.

LOOP AT t_bkpf.

*  find first entry (get index)
   READ TABLE t_bseg 
        WITH KEY bukrs = t_bkpf-bukrs
                 gjahr = t_bkpf-gjahr
                 belnr = t_bkpf-belnr
             BINARY SEARCH. 

   IF sy-subrc EQ 0.

*     get all entries. since they are adjacent,
*     exit when not equal to document key
      LOOP AT t_bseg FROM sy-tabix.
         IF t_bseg-bukrs = t_bkpf-bukrs AND
            t_bseg-gjahr = t_bkpf-gjahr AND
            t_bseg-belnr = t_bkpf-belnr.

            " Do something with t_bseg
         ELSE.
            EXIT.
         ENDIF.
      ENDLOOP.

   ENDIF.

ENDLOOP.

Message was edited by: Emir Cruz

[former_member215188]

repost. see previous post.

Message was edited by: Emir Cruz

[Former Member]

Hi Emir,

I want to avoid nested LOOPs, because it has performance problems.

Regards,

Arun

[former_member215188]

check my post again.. i added comments...

[Former Member]

Emir's solution is correct. If you must avoid loops, you can look at <a href="/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops">this.</a>

Rob

[Former Member]

LOOP AT t_bseg INTO wa_bseg.

clear t_bkpf.

read table t_bkpf INTO wa_bkpf

WHERE bukrs = wa_bseg-bukrs

AND belnr = wa_bseg-belnr

AND gjahr = wa_bseg-gjahr binary search.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

ENDIF.

ENDLOOP.

[Former Member]

SORT t_bseg BY bukrs belnr gjahr buzei.

LOOP AT t_bkpf INTO wa_bkpf.

read table t_bkpf INTO wa_bkpf binary search with key bukrs = wa_bkpf-bukrs

belnr = wa_bkpf-belnr

gjahr = wa_bkpf-gjahr.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

append wa_final to itab_final.

ENDLOOP.

[Former Member]

Hi Arun,

1. I think u have not at all used NESTED Loops.

2. Both SELECTS are independent , and not nested.

3. In fact this is the best way of doing it.

(using FOR ALL ENTRIES )

4. Nested means,

SELECT

select.. endselect.

ENDSELECT.

regards,

amit m.

[Former Member]

Hello Amit,

Thanks for you prompt reply.

I forgot to mention that for every record in BKPF(Header) table there are multiple entries in BSEG(Item) Table.

I want to achieve this without using nested LOOPS.

LOOP AT t_bkpf INTO wa_bkpf.
  LOOP AT t_bkpf INTO wa_bkpf WHERE bukrs = wa_bkpf-bukrs
                              AND   belnr = wa_bkpf-belnr
                              AND   gjahr = wa_bkpf-gjahr.
      wa_final-bukrs    =  wa_bkpf-bukrs.
      wa_final-budat    =  wa_bkpf-budat.
      wa_final-belnr    =  wa_bkpf-belnr.
      wa_final-shkzg    =  wa_bseg-shkzg.
      wa_final-dmbtr    =  wa_bseg-dmbtr.
      wa_final-xblnr    =  wa_bkpf-xblnr.
      wa_final-bktxt    =  wa_bkpf-bktxt.
      wa_final-hkont    =  wa_bseg-hkont.
      wa_final-zlsch    =  wa_bseg-zlsch.
 
   ENDLOOP.
ENDLOOP.

Regards,

Arun

Message was edited by: Arun Sambargi

[Former Member]

HI,

You can loop at the item details (BSEG) Table and then read the header table (BKPF).

BKPF will have only a single entry for BSEG becoz it is a header record.

LOOP AT t_bseg INTO wa_bseg.

<b>read table t_bkpf with key bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.</b> wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

ENDLOOP.

Thanks,

Rashmi.

[Former Member]

Hai Arun try with the following code

SELECT

bukrs

belnr

gjahr

budat

xblnr

bktxt

FROM bkpf

INTO TABLE t_bkpf

WHERE bukrs IN p_bukrs " = p_bukrs

AND gjahr IN p_gjahr "= p_gjahr

AND monat IN p_monat " = p_monat

AND bstat = space.

IF sy-subrc IS INITIAL.

SORT t_bkpf BY bukrs belnr gjahr.

endif.

if not t_bkpf[] is initial.

SELECT

bukrs

belnr

buzei

gjahr

hkont

zlsch

shkzg

dmbtr

FROM bseg

INTO TABLE t_bseg

FOR ALL ENTRIES IN t_bkpf

WHERE bukrs = t_bkpf-bukrs

AND belnr = t_bkpf-belnr

AND gjahr = t_bkpf-gjahr

AND hkont = w_hkont .

IF sy-subrc IS INITIAL.

SORT t_bseg BY bukrs belnr gjahr buzei.

ENDIF.

loop at t_bkpf.

read table it_bseg with key bukrs = t_bkpf-bukrs

AND belnr = t_bkpf-belnr

AND gjahr = t_bkpf-gjahr

binary search.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

endif.

endloop.

Thanks & regards

Sreenivasulu P

[Former Member]

hi Arun,

Is it helpfull to solve your problem!!!!

SELECT

bukrs

belnr

gjahr

budat

xblnr

bktxt

FROM bkpf

INTO TABLE t_bkpf

WHERE bukrs IN p_bukrs " = p_bukrs

AND gjahr IN p_gjahr "= p_gjahr

AND monat IN p_monat " = p_monat

AND bstat = space.

IF sy-subrc IS INITIAL.

SORT t_bkpf BY bukrs belnr gjahr.

endif.

if not t_bkpf[] is initial.

SELECT

bukrs

belnr

buzei

gjahr

hkont

zlsch

shkzg

dmbtr

FROM bseg

INTO TABLE t_bseg

FOR ALL ENTRIES IN t_bkpf

WHERE bukrs = t_bkpf-bukrs

AND belnr = t_bkpf-belnr

AND gjahr = t_bkpf-gjahr

AND hkont = w_hkont .

IF sy-subrc IS INITIAL.

SORT t_bseg BY bukrs belnr gjahr buzei.

ENDIF.

loop at t_bkpf.

read table it_bseg with key bukrs = t_bkpf-bukrs

AND belnr = t_bkpf-belnr

AND gjahr = t_bkpf-gjahr

binary search.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

append wa_final.

endif.

endloop.

Thanks & Regards,

Sarera

[Former Member]

Hi Srinivas,

This can be used, but READ statement selects only a single entry so cant be used to select BSEG records, since there are multiple records. Regarding your doubt of the usefulness to sole the problem, its that i want to improve the performance of the code. Since Nested LOOPs consume a lot of Database time.

Regards,

Arun.

[former_member181962]

Hi Arun,

This code will surely work.

I don't understand why you are not using it.

LOOP AT t_bseg INTO wa_bseg.

read table t_bkpf INTO wa_bkpf WITH key bukrs = wa_bseg-bukrs

belnr = wa_bseg-belnr

gjahr = wa_bseg-gjahr.

if sy-subrc = 0.

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-budat = wa_bkpf-budat.

wa_final-belnr = wa_bkpf-belnr.

wa_final-shkzg = wa_bseg-shkzg.

wa_final-dmbtr = wa_bseg-dmbtr.

wa_final-xblnr = wa_bkpf-xblnr.

wa_final-bktxt = wa_bkpf-bktxt.

wa_final-hkont = wa_bseg-hkont.

wa_final-zlsch = wa_bseg-zlsch.

append wa_final into it_final.

clear wa_final.

endif.

ENDLOOP.

<b>IT WILL WORK</b>

REgards,

Ravi

[Former Member]

Hi Ravi,

Read statement will select only one record from BSEG, i want to select multiple records at each loop pass of BKPF.

Hope u understand my problem.

Regards,

Arun

[former_member215188]

Ravi's code reads from BKPF not BSEG. since there is a one to many correspondence with documents and line items (a document has many line items but a line item belongs to only one document), his code is correct. As for performance, I think it'll be faster than a nested loop. Try adding a binary search to it and it'll be a screamer! I'll try to benchmark his code with mine.

Message was edited by: Emir Cruz

[former_member181962]

Hi Arun,

Let us assume that your BKPF table has 5 records.

Bseg has 2 line items for each record in BKPF that means it has 10 records in it.

NOw if you loop around those 10 records and read the bkpf table, then you will header the header level information for each line item.

So the final internal table will have 10 records only.

If you think you are having some performance issue, it is not due to the nested loops but due to the selects on the BSRG and BKPF tables,

These are very big tables and they usually take a long time to execute.

Regards,

Ravi

[Former Member]

Hi,

If u have perfomance issue with BKPF and BSEG tables , go for the alternate tables based on ur requirement BSIS,BSAS,BSAD,BSID,BSIK,BSAK,BSIP,BVOR..

Regards,

Varma

[Former Member]

Hi Arun ,

I dont think U Can avoid Nested Loops.

What u can do this and improve the performnace without puting presure on Database

1) Outer Table 1

2) Sort the Inner Table by the keys u what to search the inner table

sort Inner_table by key1 key2 key3

Loop at outter_table.

*---This will point to the first match Record and

*---you can Loop only for match Records

Read table inner_table

with key1 = outer-key1

key2 = outer-key2

key3 = outer-key3

Binary Search

TRANSPORTING NO FIELDS.

*---This is done to get Sy-tabix

if sy-subrc = 0.

LOOP AT inner_table FROM sy-tabix

*----

---

Enter Exit Condition.

IF previous key( serch key ) <> Current key

EXIT.

ENDIF.

*----

---

Enter Your Logic

Endloop.

endif.

Endloop.

Mark helpfull answers

Regards

Manoj b Gupta