2015 Nov 25 2:28 PM
There is a INNER JOIN like below is giving dump, here viafvc and caufv are views that combines two tables each,
The input range table r_objid in where clause having 1998 records, rest of all blank
If the number of records in r_objid is 1884, its not giving dump.
SELECT viafvc~aufpl " Routing number of operations
viafvc~aplzl " General counter
viafvc~plnkn " Number of the task list node
viafvc~plnal " Group Counter
viafvc~plnty " Task List Type
viafvc~plnnr " Key for Task List Group
viafvc~zaehl " Internal counter
viafvc~vornr " Operation/Activity Number
viafvc~steus " Control Key "insert CAGK9A05M3
viafvc~arbid " Object ID of the resource
viafvc~ltxa1 " Operation short text
viafvc~zerma " Type of standard value calculation
viafvc~aplfl " Sequence
viafvc~objnr " Object number
viafvc~bedid " ID of the capacity req record
viafvc~bedzl " Internal counter
viafvc~meinh " Unit of measure
viafvc~mgvrg " Operation Quantity
viafvc~lmnga " Total Yield Confirmed
viafvc~xmnga " Total scrap quantity confirmed
viafvc~fsavd " Earliest start date
viafvc~fsavz " Earliest start time
viafvc~fsedd " Earliest finish
viafvc~ssavd " Latest start
viafvc~ssedd " Latest finish
caufv~aufnr " Order Number
caufv~objnr " Object number
caufv~gltrp " Basic finish date
caufv~gstrp " Basic Start Date
caufv~rsnum " Reservation number
caufv~aufld " Date of BOM Explosion
caufv~dispo " MRP controller
afpo~plnum " Planned order no "I_CAGK9B028J
afpo~kbnkz " kanban indicator "I_CAGK9B028J
afpo~matnr " Material number
afpo~verid " Production version
afpo~berid " MRP Area
INTO TABLE i_prod
FROM viafvc AS viafvc INNER JOIN caufv AS caufv
ON caufv~aufpl EQ viafvc~aufpl
AND caufv~werks EQ p_werks
INNER JOIN afpo AS afpo
ON afpo~aufnr EQ caufv~aufnr
WHERE viafvc~arbid IN r_objid
AND viafvc~fsavd LE p_dats
* AND viafvc~loekz NE c_x " CAGK985681
AND viafvc~loekz EQ space " CAGK985681
AND afpo~matnr IN s_matnr
AND caufv~dispo IN s_dispo
AND caufv~fevor IN s_fevor
AND caufv~aufnr IN s_aufnr
AND afpo~berid IN s_berid.
Dump description :
Category ABAP Programming Error
Runtime Errors DBIF_RSQL_INVALID_RSQL
Except. CX_SY_OPEN_SQL_DB
ABAP Program
Application Component Not Assigned
Date and Time 11/25/2015 06:13:05
Error in module RSQL of the database interface
happened?
Error in the ABAP Application Program
The
current ABAP program "XXXXXXXXXX" had to be terminated
because it has
come across a statement that unfortunately cannot be executed.
can you do?
Note down which actions and inputs caused the error.
To process the problem further, contact you SAP system
administrator.
Using Transaction ST22 for ABAP Dump Analysis, you can look
at and manage termination messages, and you can also
keep them for a long time.
Note down which actions and inputs caused the error.
To process the problem further, contact you SAP system
administrator.
Using Transaction ST22 for ABAP Dump Analysis, you can look
at and manage termination messages, and you can also
keep them for a long time.
or analysis
An exception occurred that is explained in detail below.
The exception, which is assigned to class 'CX_SY_OPEN_SQL_DB', was no
in
procedure "GET_SCHE_QTY_FOR_PROD" "(FORM)", nor was it propagated by
clause.
Since the caller of the procedure could not have anticipated that the
exception would occur, the current program is terminated.
The reason for the exception is:
The SQL statement generated from the SAP Open SQL statement violates
restriction imposed by the underlying database system of the ABAP
system.
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 ...
You can generally find details in the system log (SM21) and in the
developer trace of the relevant work process (ST11).
In the case of an error, current restrictions are frequently displaye
in the developer trace.
to correct the error
Please check the entries in the system log (Transaction SM21).
Internal call code.........: "[RSQL/OPEN/VIAFVC ]"
The SAP Open SQL statement concerned 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 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.
If the error occures in a non-modified SAP program, you may be able to
find an interim solution in an SAP Note.
If you have access to SAP Notes, carry out a search with the following
keywords:
"DBIF_RSQL_INVALID_RSQL" "CX_SY_OPEN_SQL_DB"
"XXXXXX" or "XXXXXXXX"
"GET_SCHE_QTY_FOR_PROD"
(Unconverted)".
2. Corresponding system log
Display the system log by calling transaction SM21.
Restrict the time interval to 10 minutes before and five minutes
after the short dump. Then choose "System->List->Save->Local File
(Unconverted)".
3. If the problem occurs in a problem of your own or a modified SAP
program: The source code of the program
In the editor, choose "Utilities->More
Utilities->Upload/Download->Download".
4. Details about the conditions under which the error occurred or which
actions and input led to the error.
The exception must either be prevented, caught within proedure
"GET_SCHE_QTY_FOR_PROD" "(FORM)", or its possible occurrence must be declared
in the
RAISING clause of the procedure.
To prevent the exception, note the following:
If the error occures in a non-modified SAP program, you may be able to
find an interim solution in an SAP Note.
If you have access to SAP Notes, carry out a search with the following
keywords:
"DBIF_RSQL_INVALID_RSQL" "CX_SY_OPEN_SQL_DB"
"XXXXXXX" or "XXXXXXX"
"GET_SCHE_QTY_FOR_PROD"
If you cannot solve the problem yourself and want to send an error
notification to SAP, include the following information:
1. The description of the current problem (short dump)
To save the description, choose "System->List->Save->Local File
2015 Nov 25 2:45 PM
Hi,
This issue usually occurs when the selection range in the WHERE condition of the SELECT statement is quite huge, which would make the program unable to process the SELECT statement.
Please check the selection parameters in the WHERE condition and try to reduce it.
Note: If the no. of records in the selection parameter cannot be reduced then make a range table for this parameter and make a SELECT FOR ALL ENTRIES OF that range table.
2015 Nov 25 3:01 PM
Hi Mayur,
I cannot reduce the entries, and little bit complex to replace that join, may affect the functionality.
Is there any other solution?
Thanks
Guru Prasad,
2015 Nov 25 2:45 PM
So you know that the problem is in the size of R_OBJID. You can process fewer entries in R_OBJID with the PACKAGE SIZE addition of your SELECT statement or you can use FOR ALL ENTRIES instead of IN for the range.
Rob
2015 Nov 25 2:57 PM
Hi Rob,
Thanks for your suggestion.
Can you tell me how to use PACKET SIZE?
Regards
Guru Prasad.
2015 Nov 25 3:01 PM
It's PACKAGE SIZE not PACKET SIZE.
If you press F1 on SELECT, you'll see the documentation for it. And use Raymond's suggestion about APPENDING.
Rob
2015 Nov 25 2:48 PM
Check more detailed information in SM21; I suppose the generated statement exceeds the maximum length allowed for your SQL database, so raise an error.
Hint: Analyze the size of the select-options range criteria, you could replace one of those with a FOR ALL ENTRIES but beware of performance problem. Or programmatically break one of the ranges into smaller range and execute the select with APPENDING TABLE option for each batch of values.
Also wrap your SQL statement in a TRY/ENDTRY statement to prevent dump and raise an error message to the user who pasted 10.000 lines from Excel to one of the select-options.
TRY.
" select statement.
CATCH cx_sy_open_sql_db INTO oref.
text = oref->get_text( ).
MESSAGE text(132) TYPE 'S' DISPLAY LIKE 'E'.
EXIT.
ENDTRY.
"The range of the statement to be passed to the database is too wide"
Regards,
Raymond
2015 Nov 25 2:59 PM
Hi Raymond,
Data coming from the program and I cannot replace that INNERJOIN, its littile bit complex, can affect functionality. Is there any other option?
Thanks
Guru
2015 Nov 25 3:30 PM
Better Performance/More coding
Less performance/less coding
Regards,
Raymond
2015 Nov 25 4:38 PM
Hi Raymod,
in the select statement where clause you have mentioned more range tables. In that tables if there is any table having ls_range-sign = 'E' is there. please remove that statement and add ls_range-sign = 'I'.
After got the result in to LT_Result table ->delete the table against the key field.
The reason is :
We can see here that the ‘E’ (i.e. EXCLUDE) is used in the range. This explains the NOT IN on the DB side.
No of entries returned using filters from where clause:by using above condition('E') causing more effect to the DB.The NOT IN operator in the DB statement seems to be affecting the performance.
So only VIAFVC is affecting the number of records returned. I expect this should be the first table that
is accessed and it is. So the Join sequence looks to be efficient.
Regards,
Srini.