on 11-14-2005 3:06 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
78 | |
10 | |
10 | |
7 | |
7 | |
6 | |
6 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.