Short dump - CX_SY_OPEN_SQL_DB

[Former Member]

Hi,

Thanks to you I already finished my program

But in test phase I get strange situation. For some period of time it works withour a problem. And for other it dumps with error:

Runtime Errors         DBIF_RSQL_INVALID_RSQL
Exception              CX_SY_OPEN_SQL_DB

on this part:

* Get invoice headers for selection screen
  SELECT belnr gjahr blart bldat budat usnam tcode cpudt xblnr bukrs lifnr waers rmwwr wmwst
    FROM rbkp
    INTO CORRESPONDING FIELDS OF TABLE gt_rbkp
  WHERE belnr   IN s_belnr
    AND gjahr   IN s_gjahr
    AND blart   IN s_blart
    AND bldat   IN s_bldat
    AND budat   IN s_budat
    AND usnam   IN s_usnam
    AND cpudt   IN s_cpudt
    AND cputm   IN s_cputm
    AND xblnr   IN s_xblnr
    AND bukrs   IN s_bukrs
    AND lifnr   IN s_lifnr
    AND bktxt   IN s_bktxt
    AND ivtyp   IN gr_ivtyp
    AND rbstat  IN gr_rbstat.

* Set object key for MM invoices
  IF LINES( gt_rbkp ) IS NOT INITIAL.
    PERFORM set_objky.
* Get message status for MM invoices
    SELECT *                                                      {color:red}<----------{color}
       APPENDING TABLE gt_nast
       FROM nast
     WHERE objky IN gr_objky
       AND kschl = 'ZRIV'.
    SORT gt_nast BY objky erdat eruhr.
  ENDIF.

As I understand there is problem with range size, but how to manage with no dump?

[Former Member]

Hi kbordzan,

Your assumption is correct, the code below can provoke this kind of dump if you have huge number of records on gr_objky. In fact the limitation is on the SQL prepared to be sent to database (it's a parameter, and for Oracle I'm guessing it's 64k):

Anyway, you must do not change the parameter but the way you are retrieving the data from:

SELECT * APPENDING TABLE gt_nast
FROM nast
WHERE objky IN gr_objky
  AND kschl = 'ZRIV'.

To (assuming that you put the object keys on value low):

IF gr_objky[] IS NOT INITIAL.
  SELECT * APPENDING TABLE gt_nast
  FROM nast
  FOR ALL ENTRIES IN gr_objky
  WHERE objky EQ gr_objky-low
    AND kschl = 'ZRIV'.
ENDIF.

Regards, Fernando Da Ró

[Former Member]

Hi kbordzan,

Your assumption is correct, the code below can provoke this kind of dump if you have huge number of records on gr_objky. In fact the limitation is on the SQL prepared to be sent to database (it's a parameter, and for Oracle I'm guessing it's 64k):

Anyway, you must do not change the parameter but the way you are retrieving the data from:

SELECT * APPENDING TABLE gt_nast
FROM nast
WHERE objky IN gr_objky
  AND kschl = 'ZRIV'.

To (assuming that you put the object keys on value low):

IF gr_objky[] IS NOT INITIAL.
  SELECT * APPENDING TABLE gt_nast
  FROM nast
  FOR ALL ENTRIES IN gr_objky
  WHERE objky EQ gr_objky-low
    AND kschl = 'ZRIV'.
ENDIF.

Regards, Fernando Da Ró

[Former Member]

Hi Fernando,

but using EQ gr_objky-low, will choose only one record from NAST table, against 15 for IN gr_objky.

[Former Member]

Did you included the FOR ALL ENTRIES clause?

What occurred inside PERFORM set_objky?

Also with performance in mind, do not select * but only the fields you want use like objky, cmfpnr...

Edited by: Fernando Ros on Jul 20, 2010 5:03 PM

[Former Member]

My mistake, I wrongly write coding Missing IF and FOR ALL ENTRIES.

I also add this kind of check for table CMFP.

And in this perform I have:

FORM SET_OBJKY .
  CLEAR gs_rbkp.
  CLEAR gr_objky.

  gr_objky-sign   = 'I'.
  gr_objky-option = 'EQ'.

* Object key for MM invoice
  LOOP AT gt_rbkp INTO gs_rbkp.
    CONCATENATE '$$$$' gs_rbkp-belnr gs_rbkp-gjahr '000000' INTO l_objky.
    gr_objky-low = l_objky.
    APPEND gr_objky.
  ENDLOOP.

But would it be possible to write INTO CORRESPONDING FIELDS if APPENDING is using?

Edited by: kbordzan on Jul 20, 2010 5:08 PM

[Former Member]

But would it be possible to write INTO CORRESPONDING FIELDS if APPENDING is using?

Yes, but why you are using APPENDING ? This will is used when we don't want to refresh the content of internal table...

Also, it's important to you have an internal table with only the desired fields, otherwise you will spend memory for nothing.

Define a type:

TYPES: BEGIN OF ty_nast,
  objky   TYPE nast-objky,
  cmfpnr TYPE nast-cmfpnr,
END of ty_nast.

DATA:
  gt_nast TYPE SORTED TABLE OF ty_nast WITH UNIQUE KEY objky,
  gs_nast TYPE ty_nast.

FIELD-SYMBOLS:  "if you use field symbols...
  <fs_nast> TYPE ty_nast.

SELECT objky cmfpnr INTO TABLE gt_nast
FOR ALL ENTRIES IN gr_objky
WHERE objky EQ gr_objky-low
  AND kschl = 'ZRIV'.

About internal tables: http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb366d358411d1829f0000e829fbfe/content.htm

[Former Member]

As I see using both is not possible.

But I used APPENDING because I thought that I need this when range with a lot of records is used.

PS. But it is possible to write: APPENDING CORRESPONDING FIELDS OF TABLE

Edited by: kbordzan on Jul 20, 2010 6:05 PM