DBIF_RSQL_INVALID_RSQL The maximum size of an SQL statement was exceeded

[Former Member]

Dear,

I would appreciate a helping hand

I have a problem with a dump I could not find any note that I can help solve the problem.

A dump is appearing at various consultants which indicates the following.

>>> SELECT * FROM KNA1 "client specified

559 APPENDING TABLE IKNA1

560 UP TO RSEUMOD-TBMAXSEL ROWS BYPASSING BUFFER

ST22

What happened?

Error in the ABAP Application Program

The current ABAP program "/1BCDWB/DBKNA1" had to be terminated because it has

come across a statement that unfortunately cannot be executed.

Error analysis

An exception occurred that is explained in detail below.

The exception, which is assigned to class 'CX_SY_OPEN_SQL_DB', was not caught

and

therefore caused a runtime error.

The reason for the exception is:

The SQL statement generated from the SAP Open SQL statement violates a

restriction imposed by the underlying database system of the ABAP

system.

Possible error causes:

o The maximum size of an SQL statement was exceeded.

o The statement contains too many input variables.

o The input data requires more space than is available.

o ...

You can generally find details in the system log (SM21) and in the

developer trace of the relevant work process (ST11).

In the case of an error, current restrictions are frequently displayed

in the developer trace.

SQL sentence

550 if not %_l_lines is initial.

551 %_TAB2[] = %_tab2_field[].

552 endif.

553 endif.

554 ENDIF.

555 CASE ACTION.

556 WHEN 'ANZE'.

557 try.

>>> SELECT * FROM KNA1 "client specified

559 APPENDING TABLE IKNA1

560 UP TO RSEUMOD-TBMAXSEL ROWS BYPASSING BUFFER

561 WHERE KUNNR IN I1

562 AND NAME1 IN I2

563 AND ANRED IN I3

564 AND ERDAT IN I4

565 AND ERNAM IN I5

566 AND KTOKD IN I6

567 AND STCD1 IN I7

568 AND VBUND IN I8

569 AND J_3GETYP IN I9

570 AND J_3GAGDUMI IN I10

571 AND KOKRS IN I11.

572

573 CATCH CX_SY_DYNAMIC_OSQL_SEMANTICS INTO xref.

574 IF xref->kernel_errid = 'SAPSQL_ESCAPE_WITH_POOLTABLE'.

575 message i412(mo).

576 exit.

577 ELSE.

wp trace:

D *** ERROR => dySaveDataBindingValue: Abap-Field= >TEXT-SYS< not found [dypbdatab.c 510]

D *** ERROR => dySaveDataBindingEntry: dySaveDataBindingValue() Rc=-1 Reference= >TEXT-SYS< [dypbdatab.c 430]

D *** ERROR => dySaveDataBinding: dySaveDataBindingEntry() Rc= -1 Reference=>TEXT-SYS< [dypbdatab.c 137]

Y *** ERROR => dyPbSaveDataBindingForField: dySaveDataBinding() Rc= 1 [dypropbag.c 641]

Y *** ERROR => ... Dynpro-Field= >DISPLAY_SY_SUBRC_TEXT< [dypropbag.c 642]

Y *** ERROR => ... Dynpro= >SAPLSTPDA_CARRIER< >0700< [dypropbag.c 643]

D *** ERROR => dySaveDataBindingValue: Abap-Field= >TEXT-SYS< not found [dypbdatab.c 510]

D *** ERROR => dySaveDataBindingEntry: dySaveDataBindingValue() Rc=-1 Reference= >TEXT-SYS< [dypbdatab.c 430]

D *** ERROR => dySaveDataBinding: dySaveDataBindingEntry() Rc= -1 Reference=>TEXT-SYS< [dypbdatab.c 137]

Y *** ERROR => dyPbSaveDataBindingForField: dySaveDataBinding() Rc= 1 [dypropbag.c 641]

Y *** ERROR => ... Dynpro-Field= >DISPLAY_FREE_VAR_TEXT< [dypropbag.c 642]

Y *** ERROR => ... Dynpro= >SAPLSTPDA_CARRIER< >0700< [dypropbag.c 643]

I thank you in advance

If you require other information please request

[Former Member · Accepted solution]

Hi,

What transaction/report are we talking about here.

Also what is your SAP_APPL release ?

Regards.

Ruchit.

[Former Member]

Sime, I got a same problem like this.

Issue in my case is:

Some versions of system will allow a certain length of condition to hit database.

In my case lets say my DB statement is

select * from KONH into TABLE lt_konh[]

     WHERE knumh IN lt_knumh[].

If my lt_knumh[] has 3000 (more than 1999) it is giving DUMP else if is working fine. So to solve this case I gave limited my table to 1999 entries.

Now I have to hit my DB two times once with 1999 entries and again with remaining entries.

[Nuno_Raposo]

Hi There,

Try to use SE16N instead of SE16. It should work.

Regards

[former_member231744]

thank you Nuno Raposo, your reply give me a large help!!

[Former Member]

Hi,

Under certain conditions, an Open SQL statement with range tables can be reformulated into a FOR ALL ENTRIES statement:
 
    DESCRIBE TABLE range_tab LINES lines.
    IF lines EQ 0.
      [SELECT for blank range_tab]
    ELSE.
      SELECT .. FOR ALL ENTRIES IN range_tab ..
      WHERE .. f EQ range_tab-LOW ...
      [..]
      ENDSELECT.
    ENDF.
Since FOR ALL ENTRIES statements are automatically converted in accordance with the database restrictions, this solution is always met by means of a choice if the following requirements are fulfilled:
1. The statement operates on transparent tables, on database views or on a projection view on a transparent table.
2. The requirement on the range table is not negated. Moreover, the range table only contains entries with range_tab-SIGN = 'I' 
and only one value ever occurs in the field range_tab OPTION.
This value is then used as an operator with operand range_tab-LOW or range_tab-HIGH.In the above example, case 'EQ range_tab-LOW' was the typical case.
3. Duplicates are removed from the result by FOR ALL ENTRIES.This must not falsify the desired result, that is, the previous Open SQL statement can be written as SELECT DISTINCT.
For the reformulation, if the range table is empty it must be handled in a different way:with FOR ALL ENTRIES, all the records would be selected here while this applies for the original query only if the WHERE clause consisted of the 'f IN range_tab' condition.
FOR ALL ENTRIES should also be used if the Open SQL statement contains several range tables.Then (probably) the most extensive of the range tables which fill the second condition is chosen as a FOR ALL ENTRIES table.

OR

What you could do in your code is,

prior to querying;

since your select options parameter is ultimately an internal range table,

1. split the select-option values into a group of say 3000 based on your limit,

2. run your query against each chunck of 3000 parameters,

3. then put together the results of each chunk.

For further reading, you might want to have a look at the Note# 13607 as the first suggestion is what I read from the note.

[Former Member]

Hi,

This is causing due to, exceeding the maximum number of values allowed in an IN clause. Take a look at OSS note 13607.

Ask the users to adjust the selection criteria.

Regards,

Sanujit

[Former Member]

Hi,

I would do two things:

1.Minimise the number of fileds to be slected by creating the new selection screen for that table in SE16.

2.Increase the max wp time .

Just give this a try..........

Regards,

Vamshi.

[Former Member]

Hello,

Exactly TCOD are using se16.

where I can confirm the restriction of the amount of data??

I've searched but can not find notes

thank

[Former Member]

As can be seen from the dump following fields can come in handy:

561 WHERE KUNNR IN I1

562 AND NAME1 IN I2

563 AND ANRED IN I3

564 AND ERDAT IN I4

565 AND ERNAM IN I5

566 AND KTOKD IN I6

567 AND STCD1 IN I7

568 AND VBUND IN I8

569 AND J_3GETYP IN I9

570 AND J_3GAGDUMI IN I10

571 AND KOKRS IN I11.

May be they can restrict on basis on KUNNR and ERDAT or KUNNR or VBUND (trading partner). Basically they need to split the customers being selected on some criterion. So they need to supply some value in other fields. For ex: I think ERDAT is date of creation of customer. So they select customers created during a certain time period and keep on runnning that. Explain to your functional consultants and they should understand.

Select * must be avoided as much as possible when involving large data.

Regards.

Ruchit.

[Former Member]

Thank

They are showing all the data in Table KNA1

there are some parameters that can be edited to avoid this restriction of data?

thank you very much for your interest in helping

[Former Member]

Hello,

I guess it means they are trying to see entire data using Se16. Well as suggested earlier if the data being selected is too large then this sump can occur. Clearly you need to make the select query narrower in order to select lesser amount of data in one single go.

With Regards.

Ruchit

[Former Member]

The data selection seems to be very huge. ask the users to provide more details and limit the search crieterion