DBIF_RSQL_INVALID_RSQL error

[siongchao_ng]

Hi all,

I am getting error in select statement from BSAS table. Is there any limit of records that can fit inside an internal table? Any other ways to overcome this error?

The codes that triggered the error:

*   Retrieve Payment Value Date from BSAS
    IF gt_bsak[] IS NOT INITIAL.
      SELECT * INTO TABLE gt_bsas
        FROM bsas
        WHERE bukrs IN s_bukrs
        AND   gsber IN s_gsber
        AND   augdt IN s_augdt
        AND   hkont LIKE  '000056%'
        AND   belnr IN gr_belnr "payment doc
        AND   gjahr IN gr_gjahr.
    ENDIF.

[joltdx]

There could be many reasons for that dump. It's hard to know without knowing your inputs there. Maybe the most common one is that there are too many entries in your ranges. There's a limit to that depending on your DB system, as specified here in the ABAP Documentation:

The conditions specified in the selection table are passed by the database interface to the database as SQL statement input values. The maximum number of input values depends on the database system and is usually between 2000 and 10000. If the maximum number is exceeded an exception of the class CX_SY_OPEN_SQL_DB is raised.

Also, as the short dump typically indicates in ST22, have you check SM21 and ST11? Those places may hold more details about the root cause here.

Ranges are awesome but if this is the reason and you can not limit them, an option is to use SELECT FOR ALL ENTRIES instead.

(Links are to latest version of the documentation as I don't know which version you're on. There might be minor differences if you're on an older version)

Edit/Addition:

siongchao.ng, analyzing your attached dump reveals that there actually is a CX_SY_OPEN_SQL_DB exception raised and that your GR_BELNR range contains 50 269 lines. This is how you could adapt your query to use FOR ALL ENTRIES, as the note suggests, with the minimum required changes.

SELECT * INTO TABLE gt_bsas
  FROM bsas
  FOR ALL ENTRIES IN gr_belnr
  WHERE bukrs IN s_bukrs
  AND   gsber IN s_gsber
  AND   augdt IN s_augdt
  AND   hkont LIKE  '000056%'
  AND   belnr EQ gr_belnr-low "payment doc
  AND   gjahr IN gr_gjahr.

This will split the statement into smaller units. But in addition, instead of SELECT *, I recommend you should specify only those fields you really need. It will take load of the database and the SAP server as it will read and transfer less data. With 50269 lines, that will make a difference.

Secondly, you do not need a range to use FOR ALL ENTRIES. It looks like you're looping some table and then appending I EQ gw_bsak-augbl to gr_belnr. You can use this functionality directly on that table instead.

Third. Now I can't see your entire code there but if you did a SELECT earlier to get all the BELNR then there is a chance that you can improve performance even more by joining the BSAS table in that select, as matthew.billingham also suggests.

And fourth 🙂 if you're not familiar with FOR ALL ENTRIES. If the table used is empty the entire WHERE clause is ignored and all the lines from the table are read. Make sure to make sure that gr_belnr (in this case) is not empty before selecting.

[matt]

Only use FOR ALL ENTRIES as a last resort. Use a JOIN for preference. The vast majority of the time it is better.

[joltdx]

Hah, matthew.billingham, I didn't understand at first how you related FOR ALL ENTRIES to JOIN. To me they are two totally different things... So I googled it and, yes, apparently there are those who instead of using a JOIN do 2 SELECTS, the second one being a FOR ALL ENTRIES from the first result set. I have your back there. That's not the way to do it...

Personally I use FOR ALL ENTRIES either when I need to select only from a matching "pair" that is not a key, like some specific purchase order items, where I need both the order number and the item number. Can't use ranges for that from what I know... 😞

Or when the range table has too many lines, like my theory in this case, if I'm not able to limit the range in other ways...

This is interesting but we're kind of closing in on off topic now...

[matt]

jolt242

There are definitely use cases for FAE. The issue is that in very many cases I've encountered people use it instead of a decent JOIN (of whatever kind). It's often just down to ignorance There is a widespread myth that FAE performs better than JOINs. Usually it doesn't.. It also seems that some instructors aren't entirely up to date, and course content is pre 31H. So we get inexperienced people just blindly following the FAE route and running into issues.

It's better to use JOINs as the first choice, and only use FAE where it is necessary - which is specialist cases. This is what should be being taught. I always will recommend a JOIN unless the OP really needs an FAE. In this instance my guess is those ranges are empty!

(By the way, I recall one instance, where someone was selecting from one table, crafting a RANGE from it and then using that to select from the second table and then wondering why it failed at volume. That approach is really not the way to do it!)

[joltdx]

I was happy unaware that that was a thing, and I wholeheartedly stand by you in using JOINs when you need to JOIN... 🙂

Yea, in this case probably the ranges could be empty, leading to a too big result set. Or the ranges contain thousands of "I EQ" making the SQL too big... Anyway, the cause is probably right there in the dump or, if not, the logs...

[siongchao_ng]

Thanks a lot jorgen_lindqvist41. Your idea of using for all entries based on range table entries work!

[matt]

There is a limit to the number of records in an internal table, The size is limited by how much memory your application server has.

Either restrict the amount of data you're reading - insist on some entries in s_bukr, s_gsber and/or s_augdt, specify the fields you actually need rather than * or, if you absolutely must allow all selections, use a cursor.

OPEN CURSOR @DATA(cursor) FOR
        SELECT * 
        FROM bsas
        WHERE bukrs IN s_bukrs
        AND   gsber IN s_gsber
        AND   augdt IN s_augdt
        AND   hkont LIKE  '000056%'
        AND   belnr IN gr_belnr "payment doc
        AND   gjahr IN gr_gjahr.
DO.
  FETCH NEXT CURSOR cursor INTO TABLE gt_bsas PACKAGE SIZE 10000.
  IF sy-subrc IS NOT INITIAL.
    EXIT.
  ENDIF.
  ... processing of gt_bsas
ENDDO.
CLOSE CURSOR cursor.

Note that even with this, if you allow too big a selection, you may have a very long running job.

[Patrick_vN]

too bad of all the prefixes 😄

[matt]

I couldn't agree more!

[siongchao_ng]

HI Matthew, the syntax@data does not work here.

[matt]

Then you're on a pre 7.40 system I guess. You can always define the variable CURSOR outside the select. Type CURSOR.

Try reading the ABAP documentation. You might figure things out for yourself if you do.

[Sandra_Rossi]

Why do you think DBIF_RSQL_INVALID_RSQL means too many records? Could you please attach the short dump please?

[siongchao_ng]

runtime-error-20210122.txt

hi sandra rossi, as attached the abap dump