prevent inner joins in a loop-urgent

[Former Member]

How can I change the inner join into select statements and that too when these statements are in a loop .According to standards we should not be using select statements within a loop.

loop at {---}.

SELECT rseg_{belnr rseg}gjahr rseg_{buzei rseg}ebeln rseg~ebelp

rseg_{werks rseg}spgrm rbkp_{xblnr rseg}bstme ekko~ebeln

ekko_{lifnr ekko}ernam

APPENDING CORRESPONDING FIELDS OF TABLE it_rseg_ekko

FROM rseg INNER JOIN ekko ON rseg_{ebeln = ekko}ebeln

INNER JOIN rbkp ON rseg_{belnr = rbkp}belnr AND

rseg_{gjahr = rbkp}gjahr

INNER JOIN eket on rseg_{ebeln = eket}ebeln and

rseg_{ebelp = eket}ebelp

WHERE rseg~belnr = it_bkpf-rseg_belnr AND

rseg~gjahr = it_bkpf-rseg_gjahr AND

rseg~spgrm = 'X' AND

rseg~werks IN s_werks AND

ekko~bsart = 'NB'

endloop{}.

[Former Member]

Hi Appu,

Before looping tha internal table select all the data what ever u require from DB tables and read them in loop using read table statement.

cheers,

Chandra

[Former Member]

Hi Appu,

Use "For all entries" and try to put all the values in to an internal table rather than using inner joins.

refer the below code..

SELECT equnr

devloc

INTO TABLE i_egerh

FROM egerh

FOR ALL ENTRIES IN i_eanl

WHERE devloc EQ i_eanl-devloc.

IF NOT i_egerh IS INITIAL.

SELECT equnr

zwnummer

logikzw

INTO TABLE i_etdz

FROM etdz

FOR ALL ENTRIES IN i_egerh

WHERE equnr EQ i_egerh-equnr AND

( zwnummer = c_002 OR zwnummer = c_001 ).

ELSE.

RETURN.

ENDIF.

IF NOT i_etdz IS INITIAL.

SELECT logikzw

profrole

dateto

timeto

datefrom

timefrom

profile

INTO TABLE i_profass

FROM eprofass

FOR ALL ENTRIES IN i_etdz

WHERE logikzw EQ i_etdz-logikzw AND

datefrom <= ws_stdate AND

dateto >= ws_lastday AND

( profrole NE c_z006 AND

profrole NE c_z009 ).

Regards,

Sheron

[Former Member]

Hi Appu Mathew,

In order to resolve your query, Use FOR ALL ENTRIES outside the loop for fetching the data into your internal tables and in side the loop, use READ TABLE stmt. Here depending on your requirement, you can use nested READ TABLE stmts. So the performance issue is also resolved.

Hope this resolves your query.

Reward all the helpful answers.

Regards,

Nagaraj

[Former Member]

Hi

to avoid select statement in loop

1st select the data based on the codition

better to use for all entries instead of joins

because performance point of view forallentries are very good than joins

write the select query to extract the data and

read that data by putting loop at that internal table

then your program will give good results

reward if usefull

[Former Member]

Appu,

You can implement the code as mentioned below. I am not sure why you are looking into table EKBE. You don't seem to be using any data from this table nor is it part of your where clause. In my code I have not eliminated the select on EKBE however it is worth verfying if you really need this because one less select will only reduce the run time.

TYPES: BEGIN OF ty_rseg,
         belnr TYPE rseg-belnr,
         gjahr TYPE rseg-gjahr,
         buzei TYPE rseg-buzei,
         ebeln TYPE rseg-ebeln,
         ebelp TYPE rseg-ebelp,
         werks TYPE rseg-werks,
         spgrm TYPE rseg-spgrm,
         bstme TYPE rseg-bstme,
       END OF ty_rseg,

       BEGIN OF ty_rbkp,
         belnr TYPE rbkp-belnr,
         gjahr TYPE rbkp-gjahr,
         xblnr TYPE rbkp-xblnr,
       END OF ty_rbkp,

       BEGIN OF ty_eket,
         ebeln TYPE eket-ebeln,
         ebelp TYPE eket-ebelp,
         etenr TYPE eket-etenr,
       END OF ty_eket,

       BEGIN OF ty_ekko,
         ebeln TYPE ekko-ebeln,
         lifnr TYPE ekko-lifnr,
         ernam TYPE ekko-ernam,
       END OF ty_ekko,

       BEGIN OF ty_rseg_ekko,
         belnr TYPE rseg-belnr,
         gjahr TYPE rseg-gjahr,
         buzei TYPE rseg-buzei,
         ebeln TYPE rseg-ebeln,
         ebelp TYPE rseg-ebelp,
         werks TYPE rseg-werks,
         spgrm TYPE rseg-spgrm,
         xblnr TYPE rbkp-xblnr,
         bstme TYPE rseg-bstme,
         lifnr TYPE ekko-lifnr,
         ernam TYPE ekko-ernam,
       END OF ty_rseg_ekko.


DATA: it_bkpf_tmp  LIKE        TABLE OF it_bkpf,
      w_rseg       TYPE                 ty_rseg,
      it_rseg      TYPE SORTED TABLE OF ty_rseg
        WITH NON-UNIQUE KEY belnr gjahr,
      it_rseg_tmp  TYPE        TABLE OF ty_rseg,
      w_rbkp       TYPE                 ty_rbkp,
      it_rbkp      TYPE HASHED TABLE OF ty_rbkp
        WITH UNIQUE KEY belnr gjahr,
      w_eket       TYPE                 ty_eket,
      it_eket      TYPE SORTED TABLE OF ty_eket
        WITH NON-UNIQUE KEY ebeln ebelp,
      it_eket_tmp  TYPE        TABLE OF ty_eket,
      w_ekko       TYPE                 ty_ekko,
      it_ekko      TYPE HASHED TABLE OF ty_ekko
        WITH UNIQUE KEY ebeln,
      w_bkpf       LIKE it_bkpf,
      w_rseg_ekko  TYPE                 ty_rseg_ekko,
      it_rseg_ekko TYPE        TABLE OF ty_rseg_ekko.


REFRESH it_rseg_ekko.

IF NOT it_bkpf[] IS INITIAL.

  it_bkpf_tmp[] = it_bkpf[].
  SORT it_bkpf_tmp BY rseg_belnr rseg_gjahr.
  DELETE ADJACENT DUPLICATES FROM it_bkpf_tmp
    COMPARING rseg_belnr rseg_gjahr.

  SELECT belnr
         gjahr
         buzei
         ebeln
         ebelp
         werks
         spgrm
         bstme
    FROM rseg
    INTO TABLE it_rseg
    FOR ALL ENTRIES IN it_bkpf_tmp
    WHERE belnr EQ it_bkpf_tmp-rseg_belnr
    AND   gjahr EQ it_bkpf_tmp-rseg_gjahr
    AND   werks IN s_werks
    AND   spgrm EQ 'X'.

  IF sy-subrc EQ 0.

    it_rseg_tmp[] = it_rseg[].
    DELETE ADJACENT DUPLICATES FROM it_rseg_tmp
      COMPARING belnr gjahr.

    SELECT belnr
           gjahr
           xblnr
      FROM rbkp
      INTO TABLE it_rbkp
      FOR ALL ENTRIES IN it_rseg_tmp
      WHERE belnr EQ it_rseg_tmp-belnr
      AND   gjahr EQ it_rseg_tmp-gjahr.

    it_rseg_tmp[] = it_rseg[].
    SORT it_rseg_tmp BY ebeln ebelp.
    DELETE ADJACENT DUPLICATES FROM it_rseg_tmp COMPARING ebeln ebelp.

    SELECT ebeln
           ebelp
           etenr
      FROM eket
      INTO TABLE it_eket
      FOR ALL ENTRIES IN it_rseg_tmp
      WHERE ebeln EQ it_rseg_tmp-ebeln
      AND   ebelp EQ it_rseg_tmp-ebelp.

    IF sy-subrc EQ 0.

      it_eket_tmp[] = it_eket[].
      DELETE ADJACENT DUPLICATES FROM it_eket_tmp
        COMPARING ebeln.

      SELECT ebeln
             lifnr
             ernam
        FROM ekko
        INTO TABLE it_ekko
        FOR ALL ENTRIES IN it_eket_tmp
        WHERE ebeln EQ it_eket_tmp-ebeln
        AND   bsart EQ 'NB'.

    ENDIF.

  ENDIF.

ENDIF.


LOOP AT it_bkpf INTO w_bkpf.

  LOOP AT it_rseg INTO w_rseg WHERE belnr EQ w_bkpf-rseg_belnr
                              AND   gjahr EQ w_bkpf-rseg_gjahr.

    READ TABLE it_rbkp INTO w_rbkp WITH KEY belnr = w_rseg-belnr
                                            gjahr = w_rseg-gjahr
                                            TRANSPORTING
                                              xblnr.

    LOOP AT it_eket INTO w_eket WHERE ebeln EQ w_rseg-ebeln
                                AND   ebelp EQ w_rseg-ebelp.

      READ TABLE it_ekko INTO w_ekko WITH KEY ebeln = w_rseg-ebeln
                                             TRANSPORTING
                                               lifnr
                                               ernam.
      IF sy-subrc EQ 0.

        w_rseg_ekko-belnr = w_rseg-belnr.
        w_rseg_ekko-gjahr = w_rseg-gjahr.
        w_rseg_ekko-buzei = w_rseg-buzei.
        w_rseg_ekko-ebeln = w_rseg-ebeln.
        w_rseg_ekko-ebelp = w_rseg-ebelp.
        w_rseg_ekko-werks = w_rseg-werks.
        w_rseg_ekko-spgrm = w_rseg-spgrm.
        w_rseg_ekko-xblnr = w_rbkp-xblnr.
        w_rseg_ekko-bstme = w_rseg-bstme.
        w_rseg_ekko-lifnr = w_ekko-lifnr.
        w_rseg_ekko-ernam = w_ekko-ernam.
        APPEND w_rseg_ekko TO it_rseg_ekko.

      ENDIF.

    ENDLOOP.

  ENDLOOP.

ENDLOOP.

[Former Member]

Mark,

Thank you so much for your reply.

I was facing problems during the reading process and your code solved the same.

Regards,

Appu