Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Getting dump while using INNERJOIN

former_member288834
Participant
0 Kudos
1,013

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

9 REPLIES 9

mayur_priyan
Active Participant
0 Kudos
370

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.

0 Kudos
370

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,

Former Member
0 Kudos
370

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

0 Kudos
370

Hi Rob,

Thanks for your suggestion.

Can you tell me how to use PACKET SIZE?

Regards

Guru Prasad.

0 Kudos
370

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

raymond_giuseppi
Active Contributor
0 Kudos
370

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

0 Kudos
370

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

0 Kudos
370

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

0 Kudos
370

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.