SAP Note 13607 - Termination of an ABAP with DBIF_RSQL_INVALID_RSQL

Symptom:

An ABAP program terminates for a SELECT statement with the DBIF_RSQL_INVALID_RSQL error message.The system log contains the entry "The SQL statement came across a restriction of the database system".

Other terms

RSQL error 13, RSQL error 13, database limit, sql statement exceeds a db limit, markercnt, maxmarkercnt, IN itab with too many markers, too many input values

Reason and Prerequisites

When executing an ABAP program the statements formulated in ABAP/4 Open SQL are converted to "native" SQL statements by the SAP database interface, which are then passed onto the underlying database system.The termination mentioned above can be traced back to the fact that a restriction of the database system was broken by the statement in question.Different restrictions apply to the different DB systems supported by R/3.You can find out which one of these restrictions was broken by looking in the trace file dev_w[i], where [i] is the number of the work process, in which the error occurred (the dev_w[i] files are in the work directory of the application server in question).These files can also be evaluated directly in the R/3 System with Transaction ST11). For the number of the affected work process, see the syslog entry written for the the error, for example:
14:06:55 DIA 2 SE38 R68 Roll-Back ausführen
14:06:56 DIA 2 SE38 AB0 Laufzeitfehler "DBIF_RSQL_INVALID_RSQL" aufget
14:06:56 DIA 2 SE38 AB1 > Kurzdump ... erstellt
Since the entry refers to the work process with the number 2, the file dev_w2 is relevant.

In almost all cases the error occurs when you use range tables in the WHERE clause of a SELECT statement:

... WHERE ... f IN range_tab .

In this case, so many individual conditions may be contained in a range table of this type that the SQL statement derived from it exceeds certain database restrictions. Examples of such restrictions are:

1. Exceeding the maximum number of values allowed in an IN clause (for example, if the range table contains a lot of (how much exactly depends on the DB) EQ conditions)

2. Exceeding the maximum number of host-variables allowed in an SQL statement; this error appears in the work process trace in an entry of the following type:
B *** ERROR => dbtran ERROR (set_input_da_spec): statement too big
marker count = 6938 > max. marker count = 3000
With this type of entry, you should reduce the number of bind variables used to a value less than or equal to 3000, by reducing the statement accordingly.

3. Exceeding the maximum size of a statement; this error appears in the work process trace with the following message:
B *** ERROR => max. statement length (65536) exceeded
In this case, you must make sure that the generated statement is shorter than 65536 characters.

4. Repeated execution of the Open SQL statement with different (sizes of the) range tables leads to the generation of a large number of different SQL statements which the database can no longer handle.

Solution

First, you must determine the precise cause of the error using the entry in the dev_w[i] files.After this, the Open SQL SELECT statement in question may have to be divided into several statements with smaller range tables.
In this case, only a few predefined portion sizes should be used.

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.