Skip to Content

Apparently I answered a frequently asked question in this resurrected five year old thread. So I’ll take the occasion to kick off my blogging experience on SCN.

The Problem

You executed a report and you are presented with this beautiful short dump. It’s the runtime error dbif_rsql_invalid_rsql which raised the exception cx_sy_open_sql_db.

/wp-content/uploads/2012/03/select_option_shortdump_84324.png

If you carefully read through the “Error analysis” you will notice that we seem to have hit some sort of size restriction imposed by the underlying database system. The cause for this shortdump is usually a ranges table with lots and lots of entries used in a SELECT statement. Another cause from a user perspective is executing a report where lots and lots of entries were added to a select option. But since a select option on a screen is the GUI representation of a ranges table the underlying cause is again the ranges table with lots and lots of entries.

The Cause

At some point in time, either directly in the report or in a function module/method that the report calls, the ranges tables that represents the select option was used to select some data from a database table. What happens now is that the SAP Database Interface generates an SQL statement, executes that statement on the database and returns the data to your internal table. Lets take a closer look at what exactly happens.

The entries of a ranges table have a structure with the following fields:

Fieldname
Description
SIGN The sign field can have the values I or E to include or exclude this optionin the SQL result.
OPTION

The option field contains the operator to match the values from the LOW and HIGH fields against. The valid values are:

  • EQ for = LOW
  • NE for <> LOW
  • GT for > LOW
  • GE for >= LOW
  • LT for < LOW
  • LE for <= LOW
  • CP for like LOW
  • NP for not likeLOW
  • BT for between LOW and HIGH
  • NB for not between LOW and HIGH
LOW the value to match.
HIGH only used for the BT and NB options.

Lets assume the we have a ranges table IT_RANGE with this content:

SIGN OPTION LOW
HIGH
I EQ 234567890
I EQ 876543210
I BT 100000000 20000000

When the following ABAP statement is executed:

SELECT * FROM mytable INTO TABLE lt_datatab WHERE fieldname IN it_range

what happens is that the SAP Database Interface will generate a SQL statement similar to this one:

SELECT * from mytable
     WHERE ( fieldname = 234567890 OR fieldname = 876543210 )
           AND fieldname BETWEEN 10000000 AND 20000000;

Now if we have a ranges table with 10000 entries that include equal different values and use that in a select we get the following statement:

SELECT * FROM mytable
     WHERE fieldname = <first value of ranges table>
               OR fieldname = <second value of ranges table> ...and on and on and on...

If we assume a fieldname that is 6 characters long and the field value being 10 characters long we can easily calculate the SQL statements length. The extra 8 characters are ‘OR’ the operator, quotes for a char feld and spaces.

(6+10+8)*10000 = 234 Kilobytes

The actual problem is that all SQL databases have a maximum statement length.

The Solutions

  • If you have the problem when using the transaction se16 and you have an ECC system, use se16n instead. It’s nicer anyway. 🙂
  • If you experience the problem in a report that you or a colleague has control over use the PACKAGE SIZE or FOR ALL ENTRIES IN addition. Also see OSS Note 13607.
  • If the problem exists in a report you do not have control over the only option that I know of is to use less entries in the select option.

The Finish

I hope I have shed some light onto the situation. Feedback critics and comments are always more than welcome and thank you Thomas Zloch for proof reading the article.

Cheers
   Adi

To report this post you need to login first.

15 Comments

You must be Logged on to comment or reply to a post.

  1. Bhanu Malik

    Hi Adi,

    In one of our custom reports we used personnel no. on selection screen as select options, our variant are setup such that we process a lot of customers. Range is 10000000-19999999

    and 70000000-79999999.We use this range to select data from table and there is no dump.

     

    I assume the dump will not occur if we given a range on the selection screen but say for example if we give around 1000 personnel no. on selection screen it will give the runtime error.

    SIGN  OPTION LOW

      I          EQ      1000000000

      I          EQ      1000000001

    and so on …….

    Please let me know what is your opinion on this and do you agree?

    (0) 
    1. Adi Sieker Post author

      Hi Bhanu,

       

      your assumption is correct. The error only appears when you have lots of entries in the select option. It has nothing todo with how many results you retrieve.

       

      A select option that is a range like this 10000000-19999999 is translated into the SQL fragment “fieldname BETWEEN low AND high”. so it really doesn’t add much to the length of the SQL statement.

       

      Cheers

        Adi

      (0) 
  2. Otto Gold

    Hello.

    I just hit the wall with this problem and wanted to share what happened to me. I created a local type that had a similar structure to the table I was querying. The difference was that for two date fields I went for string type instead of the date type used in the table. The plan was to use INTO CORRESPONDING FIELDS OF TABLE <local type variable>. I got the very same dump. It took me a moment to realize it was the different data type that caused it and I didn’t find this option in the blog. May this helps the future generations as well

     

    cheers Otto

    (0) 
    1. Adi Sieker Post author

      Hi Otto,

       

      are you sure that you got a Runtime Error DBIF_RSQL_INVALID_RSQL and a Exception CX_SY_OPEN_SQL_DB?

      I’m guessing that you probably got some kind of conversion or type mismatch error and that doesn’t have anything to do with what I explained here. There are lots of way to get a short dump in a SELECT statement. This post is only about the SELECT-OPTIONS problem,

       

      Regards

         Adi

      (0) 
  3. akshath lt

    Hi All,

     

    Report end with short dump RUNTIME ERROR DBIF_RSQL_INVALID_RSQL which raised the exception CX_SY_OPEN_SQL_DB.If select option having more than 2000 records.

    In case user passes more than 2000 records in the selection report is giving dump because select option having limit (0-1999) which means only 2000 records you can pass in selection.

    But I have requirement here user will enter more than 2000 records in selection.

    So I have taken care of this issue like below

    * >>>> Used WHILE ….. ENDWHILE. to avoiding dump >>>>>>>

    *————————————————————————————————*

    *If passing more than 1999 records in select option -> report end with short dump.

    *The Dump says: RUNTIME ERROR DBIF_RSQL_INVALID_RSQL which raised the exception CX_SY_OPEN_SQL_DB

    * To avoid this 1.  Converted select option into range table (S_MATNR)

    *               2.  Spitting & Passing 0 – 1900 records inside the while loop. (Splitting load of select query)

    *               3.  Deleting records from range table in line number 1900

    *               4. Fetch the records from the table.

    *————————————————————————————————*

     

    LR_MATNR[] = S_MATNR[]

    *    To avoiding dump ==> if user enter more than 1999 material number in selection screen

    WHILE lr_matnr[] IS NOT INITIAL.

     

    * Copy 1 to 1900 records from lr_range_mfrpn to lr_range_mfrpn_copy.

        APPEND LINES OF lr_matnr[] FROM lc_1 TO lc_1900 TO lt_matnr_copy[].

     

    * Delete 1 to 1900 records from lr_range_mfrpn.

        DELETE lr_matnr FROM lc_1 TO lc_1900.

     

    * Get external materials

        SELECT matnr_ext                  ” External Long Material Number

          APPENDING TABLE et_matnr

          FROM materialid

          WHERE matnr_int IN lt_matnr_copy.

     

        CLEAR:lt_matnr_copy[].

     

    ENDWHILE.

    *————————————————————————————————*

    Now again i am facing similar kind of issue:

    Please find below code:

    Here I try to split and pass 1900 or 200 records every step inside the loop but program is giving error as mentioned below:

    Short text

        Error in module RSQL of the database interface

     

    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…

    Code1:

        WHILE lr_herst[] IS NOT INITIAL OR lr_txt50[] IS NOT INITIAL.

     

     

    * Copy 1 to 1900 records from lr_range_mfrpn to lr_range_mfrpn_copy.

          APPEND LINES OF lr_herst[] FROM lc_1 TO 200 TO lr_herst_copy[].

          APPEND LINES OF lr_txt50[] FROM lc_1 TO 200 TO lr_txt50_copy[].

     

     

    * Delete 1 to 1900 records from lr_range_mfrpn.

          DELETE lr_herst FROM lc_1 TO 200.

    * Delete 1 to 1900 records from lr_range_mfrpn.

          DELETE lr_txt50 FROM lc_1 TO 200.

     

     

          DELETE lr_herst_copy WHERE low = space.

          DELETE lr_txt50_copy WHERE low = space.

     

     

          OPEN CURSOR l_cursor FOR

                  SELECT anln1

                         anln2

                         anlkl

                         typbz

                         txt50

                         herst

                         anlue

                         sernr

                    FROM anla

                    FOR ALL ENTRIES IN it_bseg

                    WHERE anln1 = it_bseg-anln1

                      AND txt50 IN lr_txt50_copy[]

                      AND herst IN lr_herst_copy[]

                      AND sernr IN s_imatn[].

     

     

          DO.

            FETCH NEXT CURSOR l_cursor APPENDING TABLE lt_anla PACKAGE SIZE lc_package..

            IF sy-subrc NE 0.

              EXIT.

            ENDIF.

          ENDDO.

     

     

          CLEAR: lr_txt50_copy[],lr_herst_copy[].

     

     

          CLOSE CURSOR l_cursor.

        ENDWHILE.

     

    Code2:

     

        WHILE lr_herst[] IS NOT INITIAL OR lr_txt50[] IS NOT INITIAL.

     

     

    * Copy 1 to 200 records from lr_range_mfrpn to lr_range_mfrpn_copy.

          APPEND LINES OF lr_herst[] FROM lc_1 TO 200 TO lr_herst_copy[].

          APPEND LINES OF lr_txt50[] FROM lc_1 TO 200 TO lr_txt50_copy[].

     

     

    * Delete 1 to 1900 records from lr_range_mfrpn.

          DELETE lr_herst FROM lc_1 TO 200.

    * Delete 1 to 1900 records from lr_range_mfrpn.

          DELETE lr_txt50 FROM lc_1 TO 200.

     

                  SELECT anln1

                         anln2

                         anlkl

                         typbz

                         txt50

                         herst

                         anlue

                         sernr

                    FROM anla

                    FOR ALL ENTRIES IN it_bseg

                    WHERE anln1 = it_bseg-anln1

                      AND txt50 IN lr_txt50_copy[]

                      AND herst IN lr_herst_copy[]

                      AND sernr IN s_imatn[].

     

          CLEAR: lr_txt50_copy[],lr_herst_copy[].

     

     

     

        ENDWHILE.

     

    Kindly let me know how to handle this without using LDB?


    Thanks

    Akshath

    (0) 
  4. Nathan Jones

    You could always use the function module RSDS_RANGE_TO_WHERE if you are using ranges. This will create a where clause that should shrink it to bypass the limit in SAP.

    (0) 

Leave a Reply