Skip to Content
author's profile photo Former Member
Former Member

prevent inner joins in a loop-urgent

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 rsegbelnr rseggjahr rsegbuzei rsegebeln rseg~ebelp

rsegwerks rsegspgrm rbkpxblnr rsegbstme ekko~ebeln

ekkolifnr ekkoernam

APPENDING CORRESPONDING FIELDS OF TABLE it_rseg_ekko

FROM rseg INNER JOIN ekko ON rsegebeln = ekkoebeln

INNER JOIN rbkp ON rsegbelnr = rbkpbelnr AND

rseggjahr = rbkpgjahr

INNER JOIN eket on rsegebeln = eketebeln and

rsegebelp = eketebelp

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{}.

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 19, 2007 at 07:32 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 17, 2007 at 04:39 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 18, 2007 at 11:47 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 17, 2007 at 05:09 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 19, 2007 at 08:09 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.