Select Query without using Range table

[Former Member]

  Hi Experts,

                   A good day to u all. I have a situation where I need to fetch the FI documents from BKPF-belnr table by passing the invoice numbers to Reference key- BKPF-awkey  which i get invoice numbers  from EKBE history table .

The problem is awkey holds data as combination of invoice number and fiscal year . To solve this I wrote a below code

DATA:

            lr_key  TYPE RANGE OF awkey,

            ls_key  LIKE LINE OF lr_key.

     FIELD-SYMBOLS: <fs_ekbe_invc>                                  TYPE  ty_s_ekbe_invc.

     LOOP AT it_ekbe_invc ASSIGNING <fs_ekbe_invc>.

       CONCATENATE <fs_ekbe_invc>-belnr <fs_ekbe_invc>-gjahr

        INTO ls_key-low .

       ls_key-option = 'EQ'.

       ls_key-sign = 'I'.

       APPEND ls_key TO lr_key.

     ENDLOOP.

     IF it_ekbe_invc IS NOT INITIAL.

       SELECT  bukrs

               belnr

               gjahr

               budat

               awkey

        FROM bkpf INTO TABLE et_bkpf

        FOR ALL ENTRIES IN it_ekbe_invc

        WHERE  gjahr EQ it_ekbe_invc-gjahr

        AND    awkey IN lr_key .

     ENDIF.

But using Range table will not work , because when records exceed more than 500 , it will throw a dump. can aynone suggest better idea...

Thanks In advance.

[Former Member]

The solution is in the thread below -

- You have to break your range table and limit the same to packets.

http://scn.sap.com/message/13877375

Cheers!

Abhinab

[Former Member]

Thank you

[Former Member]

Hi Shiva,

You can create a secondary index for BKPF table with fields GJAHR and AWKEY. That will improve the performance of your query. However it may increase load on database.

Thanks,

Ajay Bose

[former_member188282]

Hi Shiva,

Append ranges table data into internal table and then try to apply for all entries from that table.

Regards,

Rajesh

[Former Member]

shiva please try this,

DATA:

             lr_key  TYPE RANGE OF awkey,

             lr_key1  TYPE RANGE OF awkey,

             ls_key  LIKE LINE OF lr_key.

      FIELD-SYMBOLS: <fs_ekbe_invc>     tYPE  ty_s_ekbe_invc.

       DATA: V_LINES TYPE I.

      LOOP AT it_ekbe_invc ASSIGNING <fs_ekbe_invc>.

        CONCATENATE <fs_ekbe_invc>-belnr <fs_ekbe_invc>-gjahr

         INTO ls_key-low .

        ls_key-option = 'EQ'.

        ls_key-sign = 'I'.

        APPEND ls_key TO lr_key.

      ENDLOOP.

  DESCRIBE TABLE lr_key LINES V_LINES.

IF V_LINES LE 500 AND it_ekbe_invc IS NOT INITIAL.

        SELECT  bukrs

                belnr

                gjahr

                budat

                awkey

         FROM bkpf INTO TABLE et_bkpf

         FOR ALL ENTRIES IN it_ekbe_invc

         WHERE  gjahr EQ it_ekbe_invc-gjahr

         AND    awkey IN lr_key .

          ELSE.

    WHILE V_LINES NE 0 AND it_ekbe_invc IS NOT INITIAL.

        APPEND LINES OF lr_key[] FROM 1 TO 500 TO lr_key1[].

        SELECT  bukrs

                belnr

                gjahr

                budat

                awkey

         FROM bkpf INTO TABLE et_bkpf

         FOR ALL ENTRIES IN it_ekbe_invc

         WHERE  gjahr EQ it_ekbe_invc-gjahr

         AND    awkey IN lr_key1.

                        DELETE lr_key[] FROM 1 TO 500.

                        CLEAR: lr_key1[],V_LINES.

                  DESCRIBE TABLE lr_key LINES V_LINES.

    ENDWHILE.

      ENDIF.

[former_member196157]

Dear shiva kumar  ,

  Here just the single correction ......and it is define the filed symbol and assing range table to fiels symbol  and then use it to the query.....

Like as ,

field-symbols: <fs_awkey> type standard table.

DATA:

            lr_key  TYPE RANGE OF awkey,

            ls_key  LIKE LINE OF lr_key.

     FIELD-SYMBOLS: <fs_ekbe_invc>                                  TYPE  ty_s_ekbe_invc.

     LOOP AT it_ekbe_invc ASSIGNING <fs_ekbe_invc>.

       CONCATENATE <fs_ekbe_invc>-belnr <fs_ekbe_invc>-gjahr

        INTO ls_key-low .

       ls_key-option = 'EQ'.

       ls_key-sign = 'I'.

       APPEND ls_key TO lr_key.

     ENDLOOP.

assign lr_key[] to <fs_awkey>.

     IF it_ekbe_invc IS NOT INITIAL.

       SELECT  bukrs

               belnr

               gjahr

               budat

               awkey

        FROM bkpf INTO TABLE et_bkpf

        FOR ALL ENTRIES IN it_ekbe_invc

        WHERE  gjahr EQ it_ekbe_invc-gjahr

*        AND    awkey IN lr_key .

        AND    awkey IN <fs_awkey> .

     ENDIF.

This may helps u.......

Thanks...

[Former Member]

Hi Shiva,

Please try the below logic.

Create another one internal table with two fields (gjahr & awkey)

     LOOP AT it_ekbe_invc ASSIGNING <fs_ekbe_invc>.

       CONCATENATE <fs_ekbe_invc>-belnr <fs_ekbe_invc>-gjahr

          INTO ws_new-awkey.

       ws_new-gjahr = <fs_ekbe_invc>-gjahr.

      APPEND ws_new TO it_new.

     ENDLOOP.

     IF  it_new IS NOT INITIAL.

       SELECT  bukrs

               belnr

               gjahr

               budat

               awkey

        FROM bkpf INTO TABLE et_bkpf

        FOR ALL ENTRIES IN  it_new

        WHERE  gjahr EQ  it_new-gjahr

        AND    awkey EQ  it_new-awkey.

     ENDIF.

The performace will be good in this case.

Regards,

Balu.

[Former Member]

Thanks to one and all .who took their valuable time in helping me .

Thank you

Shiv .