topic Re: problem with select statement in Application Development and Automation Discussions

problem with select statement

Former Member — Wed, 12 Aug 2009 11:57:30 GMT

hi,

i have a select-statment:

select * from bkpf  INTO TABLE gt_bk
                     where bukrs eq pa_bukrs
                      AND belnr IN so_belnr                       
                       and gjahr in so_gjahr.

the select-option has about 3600 entries (single bel-numbers).

i got an DBIF_RSQL_INVALID_RSQL error and an dump saying that sql-statment is too long !!! i think 3500 select-option-lines are too much.

how to avoid this ? i MUST select only the 3500 belnr's with a good performance ! BKPF-table has over 8 million records

any ideas ?

reg, Martin

Re: problem with select statement

Former Member — Wed, 12 Aug 2009 12:10:12 GMT

Hi,

You are getting the dump because you must have defined few fields in the internal table declarartion of gt_bk. So use INTO CORRESPONDING FIELDS OF TABLE when using select * and check


select * from bkpf  INTO CORRESPONDING FIELDS OF TABLE gt_bk
                     where bukrs eq pa_bukrs
                      AND belnr IN so_belnr                       
                       and gjahr in so_gjahr.

else see to it that you declare gt_bk as an internal table with the same structure as BKPF since you are using select *

Regards,

Vik

Re: problem with select statement

Former Member — Wed, 12 Aug 2009 12:11:03 GMT

Check OSS notes

374079 and 358282

Re: problem with select statement

Former Member — Wed, 12 Aug 2009 12:11:03 GMT

this error wil caused when more no of items is given to the select option .

please donot give the same account no 3500 times .

but do one thing don't give repeating nos

or search for the SAP notes

Re: problem with select statement

former_member386202 — Wed, 12 Aug 2009 12:12:51 GMT

Hi,

How u declaring the internal table gt_bk????

it should be data : gt_bk type standard table of bkpf.

Regards,

Prashant

Re: problem with select statement

sarbajitm — Wed, 12 Aug 2009 12:14:38 GMT

Hi,

Break the SQL Statement into 2 or 3 part by using FOR ALL ENTRIES clause.

Regards.

Sarbajit.

Re: problem with select statement

Former Member — Wed, 12 Aug 2009 12:16:01 GMT

oss notes 374079 and 358282 have NOTHING to do with my problem !!! ?????

the problem is the 3500 numbers ! there are NO duplicate numbers in there.

gt_bk is defined as:

data: gt_bk type table of bkpf

Re: problem with select statement

Former Member — Wed, 12 Aug 2009 12:16:11 GMT

try this

Re: problem with select statement

Former Member — Wed, 12 Aug 2009 12:16:48 GMT

Use Open Cursor in the select statement .

for example refer the below code

Maximum number of lines for DB table

STATICS: s_s_if TYPE srsc_s_if_simple,

counter

s_counter_datapakid LIKE sy-tabix,

cursor

s_cursor TYPE cursor.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT banfn

bnfpo

bsart

erdat

menge

matnr

matkl

afnam

werks

ebeln

pstyp

ebakz

INTO TABLE it_eban

FROM eban WHERE loekz = ' '.

AND banfn = i_banfn.

ENDIF.

FETCH NEXT CURSOR s_cursor

APPENDING CORRESPONDING FIELDS

OF TABLE it_eban

PACKAGE SIZE s_s_if-maxsize.

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor.

RAISE no_more_data.

ENDIF.

Re: problem with select statement

Former Member — Wed, 12 Aug 2009 12:17:14 GMT

Hi,

Your problem is described in SAP note 13607. The range (SELECT-OPTIONS) table SO_BELNR contains according to your problem description 3500 entries. The ABAP DB interface will try and construct a single SQL statement out of this (unlike with FOR ALL ENTRIES where the ABAP select will be cut into pieces automatically). The only solution that I see is that you divide the SELECT itself into manageable pieces, e.g. of 100 entries at the time and then append the results of the successive queries into the internal table GT_BK. Performance-wise I don't think this will make any difference because the access path is the same (IN condition matched via primary index).

Regards,

Mark

Re: problem with select statement

RaymondGiuseppi — Wed, 12 Aug 2009 12:18:09 GMT

Look at (old) OSS [Note 13607 - Termination of an ABAP with DBIF_RSQL_INVALID_RSQL|https://service.sap.com/sap/support/notes/13607]

- break the select-options in slice and SELECT APPENDING

- convert the select-options in a FOR ALL ENTRIES

To insure it will be possible to use one of those solution, use the FM [SELECT_OPTIONS_RESTRICT|https://forums.sdn.sap.com/search.jspa?objID=c42&q=SELECT_OPTIONS_RESTRICT] to restrict options available on the select-options - for first solution, only allow "I" include check, for the second only allow "I/EQ" single values entry.

Regards,

Raymond