topic Re: CX_SY_OPEN_SQL_DB Memory allocation failed in Application Development and Automation Discussions

CX_SY_OPEN_SQL_DB Memory allocation failed

former_member751715 — Fri, 18 Feb 2022 20:25:35 GMT

Hello experts,
a dump is returned if the code block below is executed. what would be the reason?

Thank you for your support.

 SELECT DISTINCT
 ml~aufnr,
 c~megbtr,
 c~plstar,
 c~gjahr,
 c~perio,
 c~belnr,
 c~buzei,
 c~objnr,
 c~uspob
 FROM mldoc AS ml
 INNER JOIN aufk AS a ON a~aufnr = ml~aufnr
 RIGHT JOIN coep AS c ON c~objnr = a~objnr
 AND c~kokrs = `1000`
 AND left( @p_jahpr, 4 ) = c~gjahr
 AND right( @p_jahpr, 3 ) = c~perio
 AND c~wrttp = `04`
 AND c~vrgng = `RKL`
 WHERE ml~jahrper = @p_jahpr
 AND ml~curtp = @p_curtp
* AND ml~glvor = `RKL`
 AND ml~aufnr IN @lr_aufnr
 INTO TABLE @DATA(lt_data_plstar).

Re: CX_SY_OPEN_SQL_DB Memory allocation failed

Sandra_Rossi — Fri, 18 Feb 2022 20:59:56 GMT

Try to simplify everything you can/writing differently:

AND left( @p_jahpr, 4 ) = c~gjahr → AND c~gjahr = @( substring( val = p_jahpr len = 4 ) )
AND right( @p_jahpr, 3 ) = c~perio → AND c~perio = @( substring( val = p_jahpr off = 4 ) )
RIGHT JOIN : can you change it to do a left join on MLDOC?
AND c~kokrs = `1000` → AND c~kokrs = '1000' (straight quotes instead of backticks)
AND ml~aufnr IN @lr_aufnr : What values do you have in LR_AUFNR ?
INTO TABLE @DATA(lt_data_plstar) : did you make sure the order of columns is same as the one in the SELECT, and types are exactly the same?

Re: CX_SY_OPEN_SQL_DB Memory allocation failed

former_member751715 — Sat, 19 Feb 2022 10:27:25 GMT

i noticed something:

if i does clear a lr_aufnr, solving problem. lr_aufnr defination an internal table(image.png). I found a duplicate data here and deleted one, still my problem persists.

By looping my problem was solved as follows. I haven't been able to identify the root cause though. What do you think about?

p_jahpr = 011.2021 (this problem is valid only for the 11th month)

    DATA: lr_aufnr TYPE RANGE OF aufk-aufnr,
          messages TYPE bapirettab.
*
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME.
  PARAMETERS: p_jahpr LIKE mldoc-jahrper OBLIGATORY,
              p_curtp LIKE mldoc-curtp OBLIGATORY DEFAULT `10`.
SELECTION-SCREEN END OF BLOCK blk1.
*
START-OF-SELECTION.
*
    SELECT  ml~jahrper,
            ml~curtp,
            hd~bwtar,
            hd~bwkey,
            m~matkl,
            t23~wgbez60,
            hd~matnr,
            t~maktx,
            SUM( ml~quant ) AS quant,
            ml~meins,
            ml~aufnr,
            hd~kalnr
        FROM mldoc AS ml
        INNER JOIN ckmlhd AS hd ON hd~kalnr = ml~kalnr
        INNER JOIN mara AS m ON m~matnr = hd~matnr
        LEFT OUTER JOIN makt AS t ON t~matnr = m~matnr
        AND                          t~spras = @sy-langu
        LEFT OUTER JOIN t023t AS t23 ON t23~matkl = m~matkl
        AND                             t23~spras = @sy-langu
        WHERE ml~jahrper = @p_jahpr
        AND   ml~curtp = @p_curtp
        AND   ml~categ = `ZU`
        AND   ml~ptyp  = `BF`
        AND   m~mtart IN ( `U100`, `U200` )
        AND   ml~aufnr IS NOT INITIAL
        GROUP BY  ml~jahrper,
                  ml~curtp,
                  hd~bwtar,
                  m~matkl,
                  t23~wgbez60,
                  hd~matnr,
                  t~maktx,
                  ml~meins,
                  ml~aufnr,
                  hd~kalnr,
                  hd~bwkey
        INTO TABLE @DATA(lt_data_initial).
*
    SELECT DISTINCT
           'I' AS sign,
           'EQ' AS option,
           l~aufnr AS low
        FROM @lt_data_initial AS l
        INTO CORRESPONDING FIELDS OF TABLE @lr_aufnr.
      DATA: ls_aufnr LIKE LINE OF lr_aufnr
            .
*
*loop at lr_aufnr ASSIGNING FIELD-SYMBOL(<fs_aufnr>).
LOOP AT lr_aufnr INTO ls_aufnr.
    SELECT DISTINCT
           ml~aufnr,
           c~megbtr,
           c~plstar,
           c~gjahr,
           c~perio,
           c~belnr,
           c~buzei,
           c~objnr,
           c~uspob
      FROM mldoc AS ml
      INNER JOIN aufk AS a ON a~aufnr = ml~aufnr
      RIGHT JOIN coep AS c ON c~objnr = a~objnr
      AND                     c~kokrs = `1000`
      AND                     left( @p_jahpr, 4 ) = c~gjahr
      AND                     right( @p_jahpr, 3 ) = c~perio
      AND                     c~wrttp = `04`
      AND                     c~vrgng = `RKL`
      WHERE ml~jahrper = @p_jahpr
      AND   ml~curtp = @p_curtp
**      AND   ml~glvor = `RKL`
      AND   ml~aufnr EQ @ls_aufnr-low
      INTO TABLE @DATA(lt_data_plstar).

 DATA: ls_data_plstar_2 LIKE LINE OF lt_data_plstar,
       lt_data_plstar_2 LIKE TABLE OF ls_data_plstar_2.
*
      LOOP AT lt_data_plstar INTO ls_data_plstar_2.
      APPEND ls_data_plstar_2 TO lt_data_plstar_2.
      ENDLOOP.
*
      CLEAR ls_data_plstar_2.
      CLEAR lt_data_plstar.
      CLEAR ls_aufnr.
*
ENDLOOP.
BREAK-POINT.

Re: CX_SY_OPEN_SQL_DB Memory allocation failed

Sandra_Rossi — Sat, 19 Feb 2022 12:08:39 GMT

The root cause is "memory allocation failed", you don't need to understand how the DB engine works, it would take too much time, but you can adjust till it works better. So, good that you found a solution. Also, search SAP notes like 2906305 - HANA Query fails in Join engine results (JERequestedAttributes) with Memory allocation failed;exception 1000002 - SAP ONE Support Launchpad...

Re: CX_SY_OPEN_SQL_DB Memory allocation failed

former_member751715 — Sat, 19 Feb 2022 12:13:22 GMT

Ok i understand. Thank you for help.