12-16-2020 7:43 AM
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.
12-16-2020 10:59 AM
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!
12-16-2020 7:50 AM
Please remember to examine and share the contents of the dump message.
12-16-2020 8:05 AM
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 .
12-16-2020 8:07 AM
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:
12-16-2020 8:59 AM
you internal table have header line,you need get internal table data to workarea.
12-16-2020 10:20 AM
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.
12-16-2020 10:32 AM
And none of us know the error, because you still haven't posted the dump details.
12-16-2020 12:21 PM
12-16-2020 8:32 AM
All information is given in the short dump, people can't help if you don't share it.
12-16-2020 10:54 AM
12-16-2020 10:59 AM
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!
12-16-2020 11:33 AM
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?
12-16-2020 11:53 AM
Don't use the records without purchase order in the FOR ALL ENTRIES clause.
12-16-2020 12:08 PM
dear raymond.giuseppi
I'm sorry, I do not know what to do then. So how should I write this report?
12-16-2020 12:54 PM
I think you had your crystal ball after all! It is an out of memory error.
12-16-2020 1:09 PM
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.
12-16-2020 11:25 AM
12-16-2020 11:42 AM
Hi Thanks, sandra.rossi c5e08e0478aa4727abc4482f5be390b2
I attached the error photo.
I'm glad to talk to you again about an issue
12-16-2020 11:48 AM
Please show the images instead of hyperlinks, it avoids the pain to open and close each of them separately and repeatedly. Like this:
12-16-2020 11:49 AM
TSV_TNEW_BLOCKS_NO_ROLL_MEMORY has been discussed a lot in the forum. I guess it's useless adding anything to these discussions.
12-16-2020 11:56 AM
12-16-2020 12:53 PM
You've run out of memory. Your query is so badly constructed it uses up all available memory, and the asks for more.
12-16-2020 2:42 PM
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?
12-16-2020 6:13 PM
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.
12-18-2020 8:55 AM
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.
12-17-2020 7:17 AM
Hello, sandra.rossi ,matthew.billingham ,raymond.giuseppi
Posted onthank 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.
12-17-2020 8:00 AM
12-17-2020 8:04 AM
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.
12-18-2020 6:36 AM
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
12-18-2020 7:32 AM
ABAP of the nineties then. FOR ALL ENTRIES is most of the time no more recommended because of low performance.
12-18-2020 8:52 AM
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.
12-19-2020 10:20 AM
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.
12-19-2020 10:22 AM
The terms "is wrong" or "doesn't work" are insufficient to explain what you get and what you expect.
12-19-2020 11:01 AM
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.
12-19-2020 2:24 PM
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.
12-19-2020 3:55 PM
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.
12-19-2020 4:19 PM
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.
12-19-2020 5:51 PM
12-19-2020 6:02 PM
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.
12-19-2020 6:41 PM
That would help a lot if you give an example of what data you currently have, and what result you expect.