topic Re: Select Query without using Range table in Application Development and Automation Discussions

Select Query without using Range table

Former Member — Thu, 31 Oct 2013 06:16:31 GMT

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.

Re: Select Query without using Range table

Former Member — Thu, 31 Oct 2013 06:24:22 GMT

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

Re: Select Query without using Range table

Former Member — Thu, 31 Oct 2013 06:26:26 GMT

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

Re: Select Query without using Range table

Former Member — Thu, 31 Oct 2013 06:29:23 GMT

Thank you

Re: Select Query without using Range table

former_member188282 — Thu, 31 Oct 2013 06:32:46 GMT

Hi Shiva,

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

Regards,

Rajesh

Re: Select Query without using Range table

Former Member — Thu, 31 Oct 2013 06:38:26 GMT

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.

Re: Select Query without using Range table

former_member196157 — Thu, 31 Oct 2013 06:40:07 GMT

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

Re: Select Query without using Range table

Former Member — Thu, 31 Oct 2013 07:28:05 GMT

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.

Re: Select Query without using Range table

Former Member — Thu, 31 Oct 2013 08:00:43 GMT

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

Thank you

Shiv .