topic Re: Getting dump while using INNERJOIN in Application Development and Automation Discussions

Getting dump while using INNERJOIN

former_member288834 — Wed, 25 Nov 2015 14:28:31 GMT

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

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

Re: Getting dump while using INNERJOIN

mayur_priyan — Wed, 25 Nov 2015 14:45:16 GMT

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.

Re: Getting dump while using INNERJOIN

Former Member — Wed, 25 Nov 2015 14:45:34 GMT

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

Re: Getting dump while using INNERJOIN

RaymondGiuseppi — Wed, 25 Nov 2015 14:48:55 GMT

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

Re: Getting dump while using INNERJOIN

former_member288834 — Wed, 25 Nov 2015 14:57:32 GMT

Hi Rob,

Thanks for your suggestion.

Can you tell me how to use PACKET SIZE?

Regards

Guru Prasad.

Re: Getting dump while using INNERJOIN

former_member288834 — Wed, 25 Nov 2015 14:59:41 GMT

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

Re: Getting dump while using INNERJOIN

former_member288834 — Wed, 25 Nov 2015 15:01:00 GMT

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,

Re: Getting dump while using INNERJOIN

Former Member — Wed, 25 Nov 2015 15:01:47 GMT

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

Re: Getting dump while using INNERJOIN

RaymondGiuseppi — Wed, 25 Nov 2015 15:30:02 GMT

Better Performance/More coding

Break r_objid jn smaller lr_objid of less than 1000 records, execute a select for each lr_objid, and aggregate the result,
You must also check than there are only 'I' include records in r_objid or keep the 'E'xclude records in every select or as a filter at end, also insure to get a "full primary key set" in result set to be able to delete duplicates.

Less performance/less coding

convert r_objid in a FOR ALL ENTRIES (if only some I/EQ records)
Insure that the result set contains a "full primary key set" as FOR ALL ENTRIES will trigger removal of duplicates

Regards,

Raymond

Re: Getting dump while using INNERJOIN

Former Member — Wed, 25 Nov 2015 16:38:10 GMT

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.