cancel
Showing results for 
Search instead for 
Did you mean: 

Runtime error - DBIF_RSQL_INVALID_RSQL, Exception - CX_SY_OPEN_SQL_DB

balaji_viswanath
Participant
0 Kudos

Hi,

Following code is working fine in Development system and in Production system I am getting a runtime error. Only difference I am finding is in r_lifnr value. In development system I have few lifnr and in Production system it's not so... am I getting error because of it? Is there any restriction to no. of items populated in a range? Or Basis person need to change any parameter? Enclosed the detail dump below the code...

Advance thanks.

Regards,

Balaji Viswanath.

Code:

-


SELECT bukrs

lifnr

augdt

augbl

zuonr

gjahr

belnr

buzei

waers

dmbtr

wrbtr

zlsch

shkzg

blart

gsber

sgtxt

INTO TABLE t_bsak_tmp

FROM bsak CLIENT SPECIFIED

WHERE mandt = sy-mandt AND

lifnr IN r_lifnr AND

bukrs IN s_bukrs AND

augdt IN s_augdt AND

blart IN s_blart.

IF sy-subrc <> 0.

  • No data found for selection criteria

MESSAGE i018.

LEAVE LIST-PROCESSING.

ENDIF.

Dump:

-


Runtime Errors DBIF_RSQL_INVALID_RSQL

Exception CX_SY_OPEN_SQL_DB

Occurred on 14.11.2005 at 14:35:22

-


Error in the module RSQL accessing the database interface.

-


What happened?

-


Error in ABAP application program.

The current ABAP program "/DS1/FI_CR_REPT_PGSR802_VEND_P" had to be terminated

because one of the

statements could not be executed.

This is probably due to an error in the ABAP program.

-


What can you do?

-


Print out the error message (using the "Print" function)

and make a note of the actions and input that caused the

error.

To resolve the problem, contact your SAP system administrator.

You can use transaction ST22 (ABAP Dump Analysis) to view and administer

termination messages, especially those beyond their normal deletion

date.

-


Error analysis

-


An exception occurred. This exception is dealt with in more detail below

. The exception, which is assigned to the class 'CX_SY_OPEN_SQL_DB', was

neither

caught nor passed along using a RAISING clause, in the procedure

"GET_CLRD_VEN_DATA" "(FORM)"

.

Since the caller of the procedure could not have expected this exception

to occur, the running program was terminated.

The reason for the exception is:

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

restriction imposed by the database system used in R/3.

Possible errors:

o The maximum size of an SQL statement has been exceeded.

o The statement contains too many input variables.

o The space needed for the input data exceeds the available memory.

o ...

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

developer trace of the work process (ST11).

If an error occurs the developer trace often informs you about the

current restrictions.

-


How to correct the error

-


The exception must either be prevented, caught within the procedure

"GET_CLRD_VEN_DATA"

"(FORM)", or declared in the procedure's RAISING clause.

To prevent the exception, note the following:

The SAP Open SQL statement must be divided into several smaller units.

If the problem occurred due to the use of an excessively large table

in an IN itab construct, you can use the addition FOR ALL ENTRIES

instead.

When you use this addition, the statement is split into smaller units

according to the restrictions of the database system used.

You may able to find an interim solution to the problem

in the SAP note system. If you have access to the note system yourself,

use the following search criteria:

-


"DBIF_RSQL_INVALID_RSQL" CX_SY_OPEN_SQL_DBC

"/DS1/FI_CR_REPT_PGSR802_VEND_P" or "/DS1/FI_CR_REPT_PGSR802_VEND_P"

"GET_CLRD_VEN_DATA"

-


If you cannot solve the problem yourself, please send the

following documents to SAP:

1. A hard copy print describing the problem.

To obtain this, select the "Print" function on the current screen.

-

2. A suitable hardcopy prinout of the system log.

To obtain this, call the system log with Transaction SM21

and select the "Print" function to print out the relevant

part.

3. If the programs are your own programs or modified SAP programs,

supply the source code.

To do this, you can either use the "PRINT" command in the editor or

print the programs using the report RSINCL00.

4. Details regarding the conditions under which the error occurred

or which actions and input led to the error.

-


System environment

-


SAP Release.............. "620"

Application server....... "ztm1003"

Network address.......... "145.26.160.103"

Operating system......... "AIX"

Release.................. "5.3"

Hardware type............ "00C004DC4C00"

Character length......... 16 Bits

Pointer length........... 64 Bits

Work process number...... 6

Short dump setting....... "full"

Database server.......... "ztm1003"

Database type............ "ORACLE"

Database name............ "A94"

Database owner........... "SAPR3K"

Character set............ "C"

SAP kernel............... "640"

Created on............... "Aug 7 2005 20:44:35"

Created in............... "AIX 1 5 00538A4A4C00"

Database version......... "OCI_920 "

Patch level.............. "85"

Patch text............... " "

Supported environment....

Database................. "ORACLE 8.1.7.., ORACLE 9.2.0.., ORACLE

10.1.0.."

SAP database version..... "640"

Operating system......... "AIX 1 5, AIX 2 5, AIX 3 5"

-


User, transaction...

-


Client.............. 110

User................ "INBVI0"

Language key........ "E"

Transaction......... "SE38 "

Program............. "/DS1/FI_CR_REPT_PGSR802_VEND_P"

Screen.............. "SAPMSSY0 1000"

Screen line......... 6

-


Information on where terminated

-


The termination occurred in the ABAP program "/DS1/FI_CR_REPT_PGSR802_VEND_P"

in "GET_CLRD_VEN_DATA".

The main program was "/DS1/FI_CR_REPT_PGSR802_VEND_P ".

The termination occurred in line 503 of the source code of the (Include)

program "/DS1/FI_CR_REPT_PGSR802_VEND_P"

of the source code of program "/DS1/FI_CR_REPT_PGSR802_VEND_P" (when calling

the editor 5030).

Processing was terminated because the exception "CX_SY_OPEN_SQL_DB" occurred in

the

procedure "GET_CLRD_VEN_DATA" "(FORM)" but was not handled locally, not

declared in the

RAISING clause of the procedure.

The procedure is in the program "/DS1/FI_CR_REPT_PGSR802_VEND_P ". Its source

code starts in line 501

of the (Include) program "/DS1/FI_CR_REPT_PGSR802_VEND_P ".

-


Source code extract

-


004730 &----


004740 * get country names from t005t

004750 ----


004760 FORM get_country_names.

004770

004780 t_lfa1_land[] = t_lfa1[].

004790 SORT t_lfa1_land BY land1.

004800 DELETE ADJACENT DUPLICATES FROM t_lfa1_land COMPARING land1.

004810

004820 IF NOT t_lfa1_land[] IS INITIAL.

004830 SELECT land1

004840 landx

004850 INTO TABLE t_t005t

004860 FROM t005t

004870 FOR ALL ENTRIES IN t_lfa1_land

004880 WHERE spras = sy-langu AND

004890 land1 = t_lfa1_land-land1.

004900 REFRESH t_lfa1_land.

004910 SORT t_t005t BY land1.

004920 ELSE.

004930 MESSAGE i014 WITH 'No vendor data fetched'(001).

004940 ENDIF.

004950 ENDFORM. " get_country_names

004960 &----


004970 *& Form get_clrd_ven_data

004980 &----


004990 * Cleared vendor data

005000 ----


005010 FORM get_clrd_ven_data .

005020

-


> SELECT bukrs

005040 lifnr

005050 augdt

005060 augbl

005070 zuonr

005080 gjahr

005090 belnr

005100 buzei

005110 waers

005120 dmbtr

005130 wrbtr

005140 zlsch

005150 shkzg

005160 blart

005170 gsber

005180 sgtxt

005190 INTO TABLE t_bsak_tmp

005200 FROM bsak CLIENT SPECIFIED

005210 * the order of the fields are as in the secondary Index.

005220 WHERE mandt = sy-mandt AND

-


Contents of system fields

-


SY field contents..................... SY field contents.....................

-


-


-


-


SY-SUBRC 0 SY-INDEX 0

SY-TABIX 1 SY-DBCNT 59

SY-FDPOS 0 SY-LSIND 0

SY-PAGNO 0 SY-LINNO 1

SY-COLNO 1 SY-PFKEY

SY-UCOMM

SY-TITLE Vendor Payment Details for IRD

SY-MSGTY I SY-MSGID SF

SY-MSGNO 616 SY-MSGV1 /DS1/FI_CR_REPT_PGSR802_VEND_P

SY-MSGV2 RE SY-MSGV3 EN

SY-MSGV4

-


Active calls / events

-


No.... Type........ Name..........................

Program

Include Line

Class

-


2 FORM GET_CLRD_VEN_DATA

/DS1/FI_CR_REPT_PGSR802_VEND_P

/DS1/FI_CR_REPT_PGSR802_VEND_P 503

1 EVENT START-OF-SELECTION

/DS1/FI_CR_REPT_PGSR802_VEND_P

/DS1/FI_CR_REPT_PGSR802_VEND_P 135

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Balaji,

Here is what the dump said and my answers are right below them.

<i>Possible errors:</i>

o The maximum size of an SQL statement has been exceeded.</i>

This may not be the reason in your case. This usually happens when you use the option FOR ALL ENTRIES

<i>o The statement contains too many input variables.</i>

Most likely this is your issue. I think your r_lifnr is filled with all the vendors in your system and filled as single values. In that case you may be exceeding a limit set on the system. Instead of trying to remove this setting, what I would suggest is to do the following.

Define a select-option for LIFNR on your selection screen. This way users can have the flexibility of specifying certain vendors. If they don't enter anything, it is still ok. Don't fill it with single values.

Use this select-option in your select statement. After the select statement, do a delete from itab where the vendors are not in your r_lifnr as below.

DELETE t_bsak_tmp WHERE NOT lifnr IN r_lifnr.

Also, avoid using client. You don't need that unless you really want to select from a different client other than your logon client. Your select statement is using logon client anyway, so you don't need to specify that.

<i>o The space needed for the input data exceeds the available memory.</i>

If the above changes are made and the problem is still there, then this is the next thing to look at.

Srinivas

Answers (4)

Answers (4)

MariaJoãoRocha
Contributor
0 Kudos

Hi,

You can use the option for all entries, but be sure that r_lifnr is not empty, because if the range does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).

Regards,

Maria João Rocha

Former Member
0 Kudos

Hi,

Here are the two options:

A) Using the Ranges:

The recommended maximum Range table size (R_LIFNR) is 8 KB. Since R_LIFNR record is 23 bytes (Sign 1,Option 2,Low 10,High 10), that means that if R_LIFNR has more than 347 records, it will dump. The way around this is to split R_LIFNR.

Describe table R_LIFNR lines G_LINES.

do.

G_FROM = ((sy-index - 1) * 347 ) + 1.

G_TO = sy-index * 347.

if G_to > g_lines.

G_to = g_lines.

endif.

refresh r_lifnr1.

Loop at R_LIFNR into R_LIFNR1 from G_FROM to G_TO.

append R_LIFNR1.

endloop.

SELECT ...

APPENDING TABLE t_bsak_tmp

FROM bsak CLIENT SPECIFIED

for lifnr IN r_lifnr1 AND

mandt = sy-mandt AND

bukrs IN s_bukrs AND

augdt IN s_augdt AND

blart IN s_blart.

if G_to = g_lines.

exit.

endif.

enddo.

B) Using FOR ALL ENTRIES:

if r_lifnr[] is not initial.

--- follow Rob's code, but this will not work if R_LIFNR has options like BT, GE etc. ---

endif.

Cheers,

Bhanu

balaji_viswanath
Participant
0 Kudos

Thanks to all, I have rewarded the points.

Regards,

Balaji Viswanath.

Former Member
0 Kudos

As the others have said, the error is due to having too many entries in the select options and/or range table. Assuming the range table r-lifnr is the culprid, you can:


SELECT ...
INTO TABLE t_bsak_tmp
FROM bsak CLIENT SPECIFIED

for all entries in r-lifnr 
where lifnr = r-lifnr-low and

mandt = sy-mandt AND
bukrs IN s_bukrs AND
augdt IN s_augdt AND
blart IN s_blart.

I haven't tested this, so please check thoroughly.

Rob

Former Member
0 Kudos

I guess ur production server has a lot of data,Also the dump says that max size of Sql stmt is reached which means the select dumps due to data over load.

what u can do is split the data and run thr program the required no of time u want or see whetehr u can pass some more fields to the select where clasue.

Former Member
0 Kudos

Hello Balaji,

As you have rightly mentioned, the number of entries in the ranges table of the select statement cannot be very high. Otherwise, you'd get this error. I have not yet been able to find the reason for this, but I also got the same problem a few weeks back...

Regards,

Anand Mandalika.

Former Member
0 Kudos

Balaji,

How many entries are in the Range R_LIFNR???

Thanks

Kam

manuel_bassani
Contributor
0 Kudos

Hi,

i already had this error.

Maybe the lifnr range is too much large.

In fact Open sql

select ... where lifnr in r_lifnr...

is translated in Native sql

select ... where lifnr = lifnr1 or lifnr = lifnr2 or .....

you should cut the sql statement into several smaller statements.

I solved the problem in this way:



describe table rng_lifnr lines i.

if i > 0.
  if i <= max_rng_rows.
    select *
      from ....
      into table tab
     where lifnr in rng_lifnr.
   
  else.
    from_i = 1.
    to_i = max_rng_rows.
    clear new_lifnr.
    do.
      if i < from_i.
        exit.
      endif.
      append lines of rng_lifnr from from_i to to_i to new_lifnr.
      select *
        from ...
   appending table tab
       where ebeln in new_lifnr.

       clear: new_lifnr, new_lifnr[].
       from_i = from_i + max_rng_rows.
      to_i = to_i + max_rng_rows.
    enddo.
  endif.

  sort t.
endif.

Manuel