Runtime Error in SQL command ..

[former_member425121]

Hi everybody

I have a report wich terminates with a dump message.

The error is in an SQL command, here the program aborts:

SELECT KUNNR KKBER KLIMK SAUFT SKFOR SSOBL

INTO TABLE I_KNKK

FROM KNKK

WHERE KUNNR IN R_KUNNR

AND KKBER IN R_KKBER

AND SBGRP IN S_SBGRP.

DATA: BEGIN OF I_KNKK OCCURS 0,

KUNNR LIKE KNKK-KUNNR,

KKBER LIKE KNKK-KKBER,

KLIMK LIKE KNKK-KLIMK,

SAUFT LIKE KNKK-SAUFT,

SKFOR LIKE KNKK-SKFOR,

SSOBL LIKE KNKK-SSOBL,

SBGRP LIKE KNKK-SBGRP,

END OF I_KNKK.

In first moment, i saw the table and SQL filter have the SBGRP field but the SQL fields selection has not this field. But, these same program works good with others selection parameters, in fact this SQL only terminates the program for the bigest company, wich causes the program make many BD access because all previous SQL.

The dump log says:

Runtime errors DBIF_RSQL_INVALID_RSQL

Exception CX_SY_OPEN_SQL_DB

Occurred on 23.05.2006 at 17:27:15

Error in the module RSQL accessing the database interface.

An exception occurred. The exception, which is assigned to the class 'CX_SY_OPEN_SQL_DB', neither caught nor passed along using a RAISING clause, in the procedure "(FORM)" . Since the caller of the procedure could not have expected this except to occur, the running program was terminated.

The reason for the exception is:

The SQL statement generated from the SAP Open SQL Statement violates 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 ...

How can i be sure the dump is for a lot of BD access for all the SQL commands ?

If these is the error, does somebody knows how can i eliminate this error ?

Please help ..

Regards

Frank

[RichHeilman]

I think this is happening because the fields of the internal table do not match the fields that are being selected from the database. I would suggest adding SBGRP to your field selection.

DATA: BEGIN OF I_KNKK OCCURS 0,
KUNNR LIKE KNKK-KUNNR,
KKBER LIKE KNKK-KKBER,
KLIMK LIKE KNKK-KLIMK,
SAUFT LIKE KNKK-SAUFT,
SKFOR LIKE KNKK-SKFOR,
SSOBL LIKE KNKK-SSOBL,
SBGRP LIKE KNKK-SBGRP,
END OF I_KNKK.

SELECT KUNNR KKBER KLIMK SAUFT SKFOR SSOBL <b>SBGRP</b>
INTO TABLE I_KNKK
FROM KNKK
WHERE KUNNR IN R_KUNNR
AND KKBER IN R_KKBER
AND SBGRP IN S_SBGRP.

or try using the INTO CORRESPONDING FIELDS extension.

SELECT KUNNR KKBER KLIMK SAUFT SKFOR SSOBL 
INTO <b>CORRESPONDING FIELDS OF</b> TABLE I_KNKK
FROM KNKK
WHERE KUNNR IN R_KUNNR
AND KKBER IN R_KKBER
AND SBGRP IN S_SBGRP.

Give it a try.

REgards,

Rich Heilman

[former_member425121]

Thaks Rich ..

I will to try that first.

The rare thing is that the same SQL command works so good for some data and and aborts the program for other data, and in both cases the data searched for the SQL do not exist in the table.

Regards

Frank

[former_member425121]

Thanks all for your answers

Some important fact is that the same SQL command works so good for one company and aborts the program for the bigest company;

and (i have a break point in both executions) the data searched by the SQL do not exist in the table for both cases (i look this by search myself the data via SE16)

When i execute with one company (not the bigest) the program continues and the table are empty; when i execute with the bigest company, the program aborts; but i know the result table would be empty too.

So, if the problem is the huge BD access, does somebody knows how can i eliminate the error ??

I could not change the program logic for reduces the SQL acces, because the program has been working since before and it's defined by the final user..

Is there a technical way to solve this ?? Please help.

Regards

Frank

[former_member186741]

Frank,

do your range variables have lots of entries? If they do the generated sql will be correspondingly large and can exceed the limit and cause the dump..even if your result set is going to be empty.

[former_member425121]

Neil

SELECT ....

FROM KNKK

WHERE KUNNR IN R_KUNNR AND

KKBER IN R_KKBER AND

SBGRP IN S_SBGRP.

In both cases the range variables are single values, and these are by the primary key of the table

(KNKK keys are just KUNNR and KKBER)

a) normal company (program continues)

R_KUNNR[] = IEQ0000100848

R_KKBER[] = IEQ5000

S_SBGRP[] = IEQ001

b) bigest company (program aborts)

R_KUNNR[] = IEQ0050221503

R_KKBER[] = IEQ5000

S_SBGRP[] = IEQ001

So, i think the problem are the previous SQL commands (accessing BSID, BSAD, etc.) that preced this comand, because the large amount of records accessed for the bigest company.

Is there any way to increment the size capability of a program for SQL access ?

Thanks a lot

Frank

[former_member186741]

FRANK,

COULDN'T THE DUMP BE HAPPENING IN THE 'PRECEDING SQL' RATHER THAN THE ONE WE ARE LOOKING AT? What is the preceding sql? Is it another select into table or a wraparound select? Please show us.

[former_member425121]

Neil

the dump is just exactly in the line of that SQL comand indicated, i have a break point in this line, and just pressing F5 and the programs abort.

But, i was checking the SQL commands programmed before that SQL command, and i'm finding things that i can not believe, many bugs (or worst things) that causes the SQL commands became very heavy; for example LOOP in some table A, fill some table B, but appends another table C .. then using FOR ALL ENTRIES with these erroneous tables and things like that.

So thanks all of you for your help.

I think i'm going to have a meeting with the process owner...

Regards

Frank

[Former Member]

Frank,

I think this is happnening only for huge amount of data, probably an analysis of the dump by a BASIS person might help.

Regards,

Ravi

Note : Please mark all the helpful answers

[Former Member]

Hi Frank,

Guess you might find answers in OSS notes. Try doing a search for the exception you will find a few of useful info.

This is very similar to the problem you are facing, so quite possible it could be because of large database select. You can find more info on the OSS notes....

Symptom

When you are searching for documents (information objects) by attribute instance, the system may issue an ABAP DBIF_RSQL_INVALID_RSQL runtime error with a CX_SY_OPEN_SQL_DB exception.

Other terms

DBIF_RSQL_INVALID_RSQL

CX_SY_OPEN_SQL_DB

Reason and Prerequisites

Due to a program error, an overly large SQL statement is issued in the database.

Cheers

VJ

[former_member186741]

I think the field discrepancy is a red herring here. Not guilty! I agree that using the 'CORRESPONDING FIELDS OF' is safer though.

The error could be happening because your range variables have lots of entries in them which makes the generated sql huge.

To break it up you could use the 'package size' option of the select. This will work it's way through the input in chunks (or packages) of the size you specify.

You could change it to:

SELECT KUNNR KKBER KLIMK SAUFT SKFOR SSOBL

INTO TABLE I_KNKK package size 2000 "or a variable value

FROM KNKK

WHERE KUNNR IN R_KUNNR

AND KKBER IN R_KKBER

AND SBGRP IN S_SBGRP.

• process current contents of i_knkk here

endselect.

or ,

SELECT KUNNR KKBER KLIMK SAUFT SKFOR SSOBL

appending TABLE I_KNKK package size 2000

FROM KNKK

WHERE KUNNR IN R_KUNNR

AND KKBER IN R_KKBER

AND SBGRP IN S_SBGRP.

endselect.