a short dump has occurred in a database operation

[former_member713390]

untitled.pngHI EXPERT

this is my code

Now i am getting (a short dump has occurred in a database operation)error. Can Any one suggest how can i solve this issue.

  SELECT EBAN~BANFN,
         EBAN~BNFPO,
         EKPO~EBELN,
         EKPO~EBELP
  INTO TABLE @IT_FINAL
  FROM EBAN
    LEFT OUTER JOIN EKPO ON EBAN~BANFN = EKPO~BANFN
    AND EBAN~BNFPO = EKPO~BNFPO .
    SORT IT_FINAL BY EBELN .

   IF IT_FINAL IS NOT INITIAL .
     SELECT MSEG~MBLNR
           MSEG~MJAHR
           MSEG~ZEILE
           MSEG~EBELN
    INTO TABLE IT_MSEG
    FROM MSEG 
    FOR ALL ENTRIES IN IT_FINAL
    WHERE MSEG~EBELN = IT_FINAL-EBELN
      AND MSEG~EBELP = IT_FINAL-EBELP .
ENDIF.

[abo]

Please remember to examine and share the contents of the dump message.

[JamesG]

you need this code.

TYPES: BEGIN OF wa,
         banfn   TYPE eban-banfn,
         bnfpo TYPE eban-BNFPO,
         ebeln   TYPE ekpo-ebeln,
         ebelp TYPE ekpo-ebelp,
      END OF wa.
TYPES: BEGIN OF wa1,
         mblnr   TYPE mseg-mblnr,
         mjahr TYPE mseg-mjahr,
         zeile   TYPE mseg-zeile,
         ebeln TYPE mseg-ebeln,
      END OF wa1.
data it_mseg TYPE STANDARD TABLE OF wa1.
DATA  it_final TYPE STANDARD TABLE OF wa .

[JamesG]

TYPES: BEGIN OF wa,
         banfn   TYPE eban-banfn,
         bnfpo TYPE eban-BNFPO,
         ebeln   TYPE ekpo-ebeln,
         ebelp TYPE ekpo-ebelp,
      END OF wa.
TYPES: BEGIN OF wa1,
         mblnr   TYPE mseg-mblnr,
         mjahr TYPE mseg-mjahr,
         zeile   TYPE mseg-zeile,
         ebeln TYPE mseg-ebeln,
      END OF wa1.
data it_mseg TYPE STANDARD TABLE OF wa1.
DATA  it_final TYPE STANDARD TABLE OF wa .
SELECT n~banfn,
       n~bnfpo,
       p~ebeln,
       p~ebelp FROM eban as n LEFT OUTER JOIN ekpo as p ON n~banfn = p~banfn
                                                 AND n~bnfpo = p~bnfpo
       INTO CORRESPONDING FIELDS OF TABLE @it_final .
    SORT IT_FINAL BY EBELN .

   IF IT_FINAL IS NOT INITIAL .
     SELECT MSEG~MBLNR
           MSEG~MJAHR
           MSEG~ZEILE
           MSEG~EBELN
    INTO TABLE IT_MSEG
    FROM MSEG
    FOR ALL ENTRIES IN IT_FINAL
    WHERE MSEG~EBELN = IT_FINAL-EBELN
      AND MSEG~EBELP = IT_FINAL-EBELP .
ENDIF.
cl_demo_output=>display( it_final ).
cl_demo_output=>display( it_mseg )

all program code,and result:

[JamesG]

you internal table have header line,you need get internal table data to workarea.

[former_member713390]

HI , qw1391202028

I deleted the header line but I still have error

DATA : IT_FINAL TYPE STANDARD TABLE OF ZFINAL ,
       WA_FINAL TYPE ZFINAL.

TYPES : BEGIN OF TY_MSEG ,
          MBLNR TYPE MSEG-MBLNR,
          MJAHR TYPE MSEG-MJAHR,
          ZEILE TYPE MSEG-ZEILE,
          EBELN TYPE MSEG-EBELN,
        END OF TY_MSEG .
DATA : IT_MSEG TYPE STANDARD TABLE OF TY_MSEG ,
       WA_MSEG TYPE TY_MSEG.

[matt]

And none of us know the error, because you still haven't posted the dump details.

[former_member713390]

Matthew Billingham

I attached the error photo .

[Sandra_Rossi]

All information is given in the short dump, people can't help if you don't share it.

[raymond_giuseppi]

Sorry I forgot my crystal ball at home this morning.

[raymond_giuseppi]

Well no, my crystal ball is in my pocket...

You use an LEFT OUTER JOIN between purchase requisition and purchase order, so you select also the purchase requisitions without purchase order.

Then you select material movement attached to those purchase order, so also movement without purchase order... for each record ... so the database will read many records many times (before removing duplicates)

It is not surprising that the database engine fails!

[former_member713390]

HI raymond.giuseppi

I want the information in Table EBAN to be displayed even if it is not in Table EKPO ,

What should I do?

[raymond_giuseppi]

Don't use the records without purchase order in the FOR ALL ENTRIES clause.

[former_member713390]

dear raymond.giuseppi

I'm sorry, I do not know what to do then. So how should I write this report?

[matt]

I think you had your crystal ball after all! It is an out of memory error.

[raymond_giuseppi]

You could create another internal table with only the records containing a purchase order number, and if this table is not empty, use it in the FOR ALL ENTRIES option of the SELECT statement.

Or you should consider using a single SELECT statement with 2 'left outer' joins.

[FredericGirod]

raymond.giuseppi you have one too ? 🙂

[former_member713390]

Hi Thanks, sandra.rossi c5e08e0478aa4727abc4482f5be390b2

I attached the error photo.

I'm glad to talk to you again about an issue

[Sandra_Rossi]

Please show the images instead of hyperlinks, it avoids the pain to open and close each of them separately and repeatedly. Like this:

[Sandra_Rossi]

TSV_TNEW_BLOCKS_NO_ROLL_MEMORY has been discussed a lot in the forum. I guess it's useless adding anything to these discussions.

[former_member713390]

dear Sandra Rossi

Sorry. I did not understand what to do then

[matt]

You've run out of memory. Your query is so badly constructed it uses up all available memory, and the asks for more.

[Sandra_Rossi]

After you have searched the Web, I hope you have understood that you read too much data at a time.

Now, can you explain more what is your issue to read less data?

[I355602]

TYPES: BEGIN OF ty1,
         banfn   TYPE eban-banfn,
         bnfpo TYPE eban-BNFPO,
         ebeln   TYPE ekpo-ebeln,
         ebelp TYPE ekpo-ebelp,
      END OF ty1.
TYPES: BEGIN OF ty2,
         mblnr   TYPE mseg-mblnr,
         mjahr TYPE mseg-mjahr,
         zeile   TYPE mseg-zeile,
         ebeln TYPE mseg-ebeln,
      END OF ty2.
DATA it_eban TYPE STANDARD TABLE OF ty1.
DATA it_eban_tmp TYPE STANDARD TABLE OF ty1.
DATA it_mseg TYPE STANDARD TABLE OF ty2 .
SELECT EBAN~BANFN EBAN~BNFPO EKPO~EBELN EKPO~EBELP
  INTO TABLE it_eban
  FROM EBAN
  LEFT OUTER JOIN EKPO ON EBAN~BANFN = EKPO~BANFN
                      AND EBAN~BNFPO = EKPO~BNFPO.

  SORT IT_EBAN BY EBELN EBELP.
  IT_EBAN_TMP = IT_EBAN.

  DELETE IT_EBAN_TMP WHERE EBELN IS INITIAL.
  DELETE ADJACENT DUPLICATES FROM IT_EBAN_TMP COMPARING EBELN EBELP.
  IF IT_EBAN_TMP IS NOT INITIAL .
     SELECT MSEG~MBLNR MSEG~MJAHR MSEG~ZEILE MSEG~EBELN
       INTO TABLE IT_MSEG
       FROM MSEG 
       FOR ALL ENTRIES IN IT_EBAN_TMP
       WHERE MSEG~EBELN = IT_EBAN_TMP-EBELN
         AND MSEG~EBELP = IT_EBAN_TMP-EBELP .
  ENDIF.

[matt]

I don't see the point of IT_EBAN_TMP.

You should use SORTED tables, not SORT.

You can use DISTINCT in the SELECT to ensure you don't get duplicates.

You can use ORDER BY in the SELECT to have the database engine do the work instead of the application server.

[former_member713390]

Hello, sandra.rossi ,matthew.billingham ,raymond.giuseppi

Posted on

thank you all. This is my complete code.

In this code, I want to show the information in tables EBAN, EKPO, LIPS and MSEG.

But I want to display all the values in the EBAN table, even if they are not in the EKPO table.

I also do not want to have more than 3 joins.

ZFINAL is (EBAN~BANFN ,EBAN~BNFPO ,EKPO~EBELN ,EKPO~EBELP,LIPS~VBELN,LIPS~POSNE,MSEG~MBLNR,MSEG~MJAHR,MSEG~ZEILE

and EBELPNUM (convert EBELP to 6 numeric for join with lips )

TYPE-POOLS : SLIS .

DATA : IT_FINAL TYPE STANDARD TABLE OF ZFINAL ,
       WA_FINAL TYPE ZFINAL.

TYPES : BEGIN OF TY_LIPS ,
          VBELN TYPE LIPS-VBELN,
          POSNR TYPE LIPS-POSNR,
          VGBEL TYPE LIPS-VGBEL,
          VGPOS TYPE LIPS-VGPOS,
        END OF TY_LIPS .
DATA : IT_LIPS TYPE  STANDARD TABLE OF TY_LIPS ,
       WA_LIPS TYPE TY_LIPS.


TYPES : BEGIN OF TY_MSEG ,
          MBLNR TYPE MSEG-MBLNR,
          MJAHR TYPE MSEG-MJAHR,
          ZEILE TYPE MSEG-ZEILE,
          EBELN TYPE MSEG-EBELN,
        END OF TY_MSEG .
DATA : IT_MSEG TYPE STANDARD TABLE OF TY_MSEG ,
       WA_MSEG TYPE TY_MSEG.
DATA : IT_FCAT TYPE SLIS_T_FIELDCAT_ALV,
       WA_FCAT TYPE SLIS_FIELDCAT_ALV.

SELECT-OPTIONS : S_BANFN FOR WA_FINAL-BANFN,
       S_BNFPO FOR WA_FINAL-BNFPO,
       S_EBELN FOR WA_FINAL-EBELN,
       S_VBELN FOR WA_LIPS-VBELN,
       S_MBLNR FOR WA_MSEG-MBLNR,
       S_MJAHR FOR WA_MSEG-MJAHR.

START-OF-SELECTION .
  PERFORM GET_DATA .
  PERFORM VALIDATE_FCAT.
  PERFORM DISPLAY_DATA.
FORM GET_DATA.


  SELECT EBAN~BANFN,
         EBAN~BNFPO,
         EKPO~EBELN,
         EKPO~EBELP
  INTO TABLE @IT_FINAL
  FROM EBAN
    LEFT OUTER JOIN EKPO ON EBAN~BANFN = EKPO~BANFN
    AND EBAN~BNFPO = EKPO~BNFPO
    WHERE EBAN~BANFN IN @S_BANFN
    AND EBAN~BNFPO IN @S_BNFPO
    AND EKPO~EBELN IN @S_EBELN .
    SORT IT_FINAL BY EBELN EBELP.
  

*  SELECT EBAN~BANFN
*         EBAN~BNFPO
*         EKPO~EBELN
*         EKPO~EBELP
*  INTO TABLE IT_FINAL
*  FROM EBAN
*    INNER JOIN EKPO ON EBAN~BANFN = EKPO~BANFN
*    AND EBAN~BNFPO = EKPO~BNFPO
*    WHERE EBAN~BANFN IN S_BANFN
*    AND EBAN~BNFPO IN S_BNFPO
*    AND EKPO~EBELN IN S_EBELN
  .
  LOOP AT IT_FINAL INTO WA_FINAL .
    WA_FINAL-EBELPNUM = WA_FINAL-EBELP .
    MODIFY IT_FINAL FROM WA_FINAL .
  ENDLOOP.



   IF IT_FINAL IS NOT INITIAL .
    SELECT VBELN
           POSNR
           VGBEL
           VGPOS
      INTO TABLE IT_LIPS
    FROM LIPS
    FOR ALL ENTRIES IN IT_FINAL
    WHERE VGBEL = IT_FINAL-EBELN
    AND VGPOS = IT_FINAL-EBELPNUM
    AND VBELN IN S_VBELN.

     SELECT MSEG~MBLNR
           MSEG~MJAHR
           MSEG~ZEILE
           MSEG~EBELN
    INTO TABLE IT_MSEG
    FROM MKPF
      INNER JOIN MSEG ON MKPF~MBLNR = MSEG~MBLNR
      AND MKPF~MJAHR = MSEG~MJAHR
    FOR ALL ENTRIES IN IT_FINAL
    WHERE MSEG~EBELN = IT_FINAL-EBELN
      AND MSEG~EBELP = IT_FINAL-EBELP
      AND MSEG~MBLNR IN S_MBLNR
      AND MSEG~MJAHR IN S_MJAHR.

    LOOP AT IT_FINAL INTO WA_FINAL.

      READ TABLE IT_LIPS INTO WA_LIPS
      WITH KEY VGBEL = WA_FINAL-EBELN
      VGPOS = WA_FINAL-EBELPNUM.
      WA_FINAL-VBELN = WA_LIPS-VBELN .
      WA_FINAL-POSNR = WA_LIPS-POSNR.

      READ TABLE IT_MSEG INTO WA_MSEG
      WITH KEY EBELN = WA_FINAL-EBELN .
      WA_FINAL-MBLNR = WA_MSEG-MBLNR.
      WA_FINAL-MJAHR = WA_MSEG-MJAHR.
      WA_FINAL-ZEILE = WA_MSEG-ZEILE.

      MODIFY IT_FINAL FROM WA_FINAL .

    ENDLOOP.
  ENDIF.

ENDFORM.

FORM VALIDATE_FCAT.



  CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
    EXPORTING
      I_PROGRAM_NAME   = SY-REPID
      I_STRUCTURE_NAME = 'ZFINAL'
      I_INCLNAME       = SY-REPID
    CHANGING
      CT_FIELDCAT      = IT_FCAT[].

  LOOP AT IT_FCAT INTO WA_FCAT .
    CASE WA_FCAT-FIELDNAME.
      WHEN 'EBELPNUM'.
        WA_FCAT-NO_OUT = 'X'.
    ENDCASE.
    Modify IT_FCAT from WA_FCAT.
  ENDLOOP.


ENDFORM.

FORM DISPLAY_DATA.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      I_CALLBACK_PROGRAM = SY-REPID
      IT_FIELDCAT        = IT_FCAT
    TABLES
      T_OUTTAB           = IT_FINAL.



ENDFORM.

[Sandra_Rossi]

Why do you say "I also do not want to have more than 3 joins" ?

[Sandra_Rossi]

You may limit the number of lines by using UP TO 1000000 ROWS, and warn the user if 1000000 lines have been extracted (which means that there are more lines). I think it's non sense to have more rows on the display (even 1000000 does not make much sense). If the user needs some aggregations, he should tell her/his requirements or use dedicated tools to be able to do his own aggregations.

[former_member713390]

hi sandra.rossi

Because my manager does not allow me to have more than 3 joins,and I should try to use For ALL Enterise instead

[Sandra_Rossi]

ABAP of the nineties then. FOR ALL ENTRIES is most of the time no more recommended because of low performance.

[matt]

Your manager believes a myth. There is no reason why a query can't have 10 joins or more. SQL programmers do it all the time. FOR ALL ENTRIES is the wrong way to do it - it will hinder performance.

I have joined 6 tables in a select - no issues whatseover.

[former_member713390]

HI sandra.rossi matthew.billingham

Thank you . I changed the code as follows. And that problem was solved.

But now my IT_LIPS output is wrong. Is my join wrong?

TYPE-POOLS : SLIS .
DATA : IT_FINAL TYPE STANDARD TABLE OF ZFINAL ,
       WA_FINAL TYPE ZFINAL.

TYPES : BEGIN OF TY_LIPS ,
          VBELN TYPE LIPS-VBELN,
          POSNR TYPE LIPS-POSNR,
          VGBEL TYPE LIPS-VGBEL,
          VGPOS TYPE LIPS-VGPOS,
        END OF TY_LIPS .
DATA : IT_LIPS TYPE  STANDARD TABLE OF TY_LIPS ,
       WA_LIPS TYPE TY_LIPS.


TYPES : BEGIN OF TY_MSEG ,
          MBLNR TYPE MSEG-MBLNR,
          MJAHR TYPE MSEG-MJAHR,
          ZEILE TYPE MSEG-ZEILE,
          EBELN TYPE MSEG-EBELN,
        END OF TY_MSEG .
DATA : IT_MSEG TYPE STANDARD TABLE OF TY_MSEG ,
       WA_MSEG TYPE TY_MSEG.
DATA : IT_FCAT TYPE SLIS_T_FIELDCAT_ALV,
       WA_FCAT TYPE SLIS_FIELDCAT_ALV.

SELECT-OPTIONS : S_BANFN FOR WA_FINAL-BANFN,
       S_BNFPO FOR WA_FINAL-BNFPO,
       S_EBELN FOR WA_FINAL-EBELN,
       S_VBELN FOR WA_LIPS-VBELN,
       S_MBLNR FOR WA_MSEG-MBLNR,
       S_MJAHR FOR WA_MSEG-MJAHR.

START-OF-SELECTION .
  PERFORM GET_DATA .
  PERFORM VALIDATE_FCAT.
  PERFORM DISPLAY_DATA.
FORM GET_DATA.


  SELECT EBAN~BANFN,
         EBAN~BNFPO,
         EKPO~EBELN,
         EKPO~EBELP,
         MSEG~MBLNR,
         MSEG~MJAHR,
         MSEG~ZEILE
 INTO TABLE @IT_FINAL
  FROM EBAN
    LEFT OUTER JOIN EKPO ON EBAN~BANFN = EKPO~BANFN
    AND EBAN~BNFPO = EKPO~BNFPO
    LEFT OUTER JOIN MSEG ON MSEG~EBELN = EKPO~EBELN
      AND MSEG~EBELP = EKPO~EBELP
    WHERE EBAN~BANFN IN @S_BANFN
    AND EBAN~BNFPO IN @S_BNFPO
    AND EKPO~EBELN IN @S_EBELN
    AND MSEG~MBLNR IN @S_MBLNR
    AND MSEG~MJAHR IN @S_MJAHR.
    SORT IT_FINAL BY EBELN EBELP.

  LOOP AT IT_FINAL INTO WA_FINAL .
    WA_FINAL-EBELPNUM = WA_FINAL-EBELP .
    MODIFY IT_FINAL FROM WA_FINAL .
  ENDLOOP.


    IF IT_FINAL IS NOT INITIAL .
    
    SELECT VBELN
           POSNR
           VGBEL
           VGPOS
      INTO TABLE IT_LIPS
    FROM LIPS
    FOR ALL ENTRIES IN IT_FINAL
    WHERE VGBEL = IT_FINAL-EBELN
    AND VGPOS = IT_FINAL-EBELPNUM
    AND VBELN IN S_VBELN.

    LOOP AT IT_FINAL INTO WA_FINAL.

      READ TABLE IT_LIPS INTO WA_LIPS
      WITH KEY VGBEL = WA_FINAL-EBELN
      VGPOS = WA_FINAL-EBELPNUM.
      WA_FINAL-VBELN = WA_LIPS-VBELN .
      WA_FINAL-POSNR = WA_LIPS-POSNR.

      MODIFY IT_FINAL FROM WA_FINAL .

    ENDLOOP.
  ENDIF.
ENDFORM.

FORM VALIDATE_FCAT.



  CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
    EXPORTING
      I_PROGRAM_NAME   = SY-REPID
      I_STRUCTURE_NAME = 'ZFINAL'
      I_INCLNAME       = SY-REPID
    CHANGING
      CT_FIELDCAT      = IT_FCAT[].

  LOOP AT IT_FCAT INTO WA_FCAT .
    CASE WA_FCAT-FIELDNAME.
      WHEN 'EBELPNUM'.
        WA_FCAT-NO_OUT = 'X'.
    ENDCASE.
    Modify IT_FCAT from WA_FCAT.
  ENDLOOP.


ENDFORM.

FORM DISPLAY_DATA.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      I_CALLBACK_PROGRAM = SY-REPID
      IT_FIELDCAT        = IT_FCAT
    TABLES
      T_OUTTAB           = IT_FINAL.



ENDFORM.

[Sandra_Rossi]

The terms "is wrong" or "doesn't work" are insufficient to explain what you get and what you expect.

[former_member713390]

sandra.rossi

I expect that only when

VGBEL = WA_FINAL-EBELN

VGPOS = WA_FINAL-EBELPNUM

This condition is met in my final table value

WA_FINAL-VBELN = WA_LIPS-VBELN

WA_FINAL-POSNR = WA_LIPS-POSNR Fill in these fields.

But unfortunately for each record in my EBAN table this value is displayed if I have only eleven records containing VGBEL = WA_FINAL-EBELN VGPOS = WA_FINAL-EBELPNUM These are the conditions.

[Sandra_Rossi]

I don't understand why you think "SELECT ... FROM LIPS" is wrong, because "VGBEL = IT_FINAL-EBELN AND VGPOS = IT_FINAL-EBELPNUM AND VBELN IN S_VBELN" does exactly what it seems to do, there's no special behavior about it. I guess that the issue is more about what columns are to be used to join correctly the database tables EBAN, EKPO, MSEG, LIPS.

I see that VGBEL and VGPOS of LIPS are respectively CHAR 10 and NUMC 6, that you want to join with EBELN and EBELP of EKPO, which are respectively CHAR 10 and NUMC 5, you must make sure that you join on equal length, otherwise the equality will always be wrong ('00010' is different from '000010'). I guess it's what you want to achieve by defining a new column EBELPNUM, which must be of type NUMC 6 (type like LIPS-VGPOS).

Note that I don't see the order and definition of columns of ZFINAL, I hope they are correctly defined and in the same order as "EBAN~BANFN, EBAN~BNFPO, EKPO~EBELN, EKPO~EBELP, MSEG~MBLNR, MSEG~MJAHR, MSEG~ZEILE", and I guess that the 3 last columns are EBELPNUM (NUMC 6), LIPS-VBELN and LIPS-POSNR.

[former_member713390]

DEAR Sandra Rossi Thank you for your excellent explanation.

Yes, the order of the IT_FINAL fields is as you said.

For records where there is VBELN, the correct value is displayed, but for records whose VBELN is null, it displays a value incorrectly instead of the empty field.

[Sandra_Rossi]

Don't forget to test if READ is successful or not, because WA_LIPS is not cleared if no line is found:

READ TABLE IT_LIPS INTO WA_LIPS
      WITH KEY VGBEL = WA_FINAL-EBELN
      VGPOS = WA_FINAL-EBELPNUM.
IF sy-subrc = 0.                    " <===============
  WA_FINAL-VBELN = WA_LIPS-VBELN .
  WA_FINAL-POSNR = WA_LIPS-POSNR.
  MODIFY IT_FINAL FROM WA_FINAL .
ENDIF.

[former_member713390]

Sandra Rossi

Unfortunately it did not work

[former_member713390]

Sandra Rossi

This is how I changed the code. But unfortunately still for the records that VBELN should be null. VBELN has a value

    LOOP AT IT_FINAL INTO WA_FINAL.

      READ TABLE IT_LIPS INTO WA_LIPS
      WITH KEY VGBEL = WA_FINAL-EBELN
      VGPOS = WA_FINAL-EBELPNUM.
  IF SY-SUBRC = 0.
    V_INDEX = SY-TABIX.
    LOOP AT IT_LIPS INTO WA_LIPS FROM V_INDEX. "no where condition for loop
      IF WA_LIPS-VGBEL <> WA_FINAL-EBELN
        AND WA_LIPS-VGPOS <> WA_FINAL-EBELPNUM.
        EXIT.
      ENDIF.
      WA_FINAL-VBELN = WA_LIPS-VBELN .
      WA_FINAL-POSNR = WA_LIPS-POSNR.

      MODIFY IT_FINAL FROM WA_FINAL .
    ENDLOOP.
      ENDIF.
  CLEAR: WA_FINAL, WA_LIPS.
  ENDLOOP.

[Sandra_Rossi]

That would help a lot if you give an example of what data you currently have, and what result you expect.