Skip to Content
-1

How can I select two records based upon the latest BUDAT in MSEG

Sep 12 at 12:11 PM

115

avatar image

Hi Experts.

I got a set of article in my internal table gt_mara. I want to select latest two records of data from MSEG table based upon BUDAT ( entry date) for all the entries of GT_mara with good performance.

I can use MAX function . But will return single record. But i need two.,

regards

shefeek m k

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Sandra Rossi Sep 13 at 01:26 PM
1

I guess there's no simple solution. Either use a big SELECT and remove the entries from the internal table (see Horst answer), or if you have a small volume see Bertrand answer), or use native SQL (you'll have to get rid of FOR ALL ENTRIES too; for Oracle, use RANK you may have a look here: https://stackoverflow.com/questions/2306744/oracle-sql-how-to-retrieve-highest-5-values-of-a-column ).

Share
10 |10000 characters needed characters left characters exceeded
Horst Keller SAP Employee Sep 12 at 03:23 PM
0

Enhance your internal table with an appropriate (secondary) sorted key and get the two lines using the (secondary) table index.

Show 4 Share
10 |10000 characters needed characters left characters exceeded

How would OP get two lines with latest dates using a table index? This is a descending sort situation but the keys are in ascending order, I believe. At least I don't recall a different option in ECC 6.0... So index or not OP would still end up reading all records (per MATNR and whatever other criteria can be used) and then going through them searching for 2 latest dates, right?

0
SAP Employee
Jelena Perfiljeva

There are so many ways. ... INDEX lines( itab ) - 1, INDEX lines( itab ), grouping, temporary sorted subtables ...

0

Ah, OK, I thought you meant an internal table and a database table index. But after re-reading the answer again I see it was all just about an internal table. Nevermind then. Thank you!

0

Thanks for your answer.. Still I am confused, how to do.. Let me provide my code, how I handled.. Pls check and give me more performance way. Expecting GT_mara is 15000 records and each material in mseg got 20000 records..

--->selecting the mseg data first.

SELECT matnr bwart werks lgort umwrk shkzg budat_mkpf ebeln lifnr

FROM mseg INTO TABLE gt_mseg FOR ALL ENTRIES IN gt_mara

WHERE matnr EQ gt_mara-matnr AND werks IN so_werks AND

bwart EQ '101' AND lifnr NE space .

----->selecting the latest two records for comparing both. if its same first record is updated as 'SAME',

-----> if its different updating it as 'DIFF'. at last keeping the records which got 'SAME'. deleting remaining all.

SORT gt_mseg DESCENDING BY matnr budat .

clear lwa_mseg.

LOOP AT gt_mseg INTO gwa_mseg.

lv_tabix = sy-tabix.

lwa_mseg_tmp = gwa_mseg.

AT NEW matnr.

lwa_mseg = lwa_mseg_tmp.

lv_count = 1.

gwa_mseg-compr = 'SAME'.

MODIFY gt_mseg FROM gwa_mseg INDEX lv_tabix TRANSPORTING compr.

ENDAT.

if gwa_mseg-matnr = lwa_mseg-matnr.

if lv_count = 2.

if lwa_mseg-umwrk = gwa_mseg-umwrk.

clear lv_count.

CONTINUE.

else.

gwa_mseg-compr = 'DIFF'.

lv_tabix = lv_tabix - 1.

modify gt_mseg FROM gwa_mseg INDEX lv_tabix TRANSPORTING compr.

clear lv_count.

CONTINUE.

ENDIF.

ENDIF.

IF lv_count = 1.

lv_count = 2.

ENDIF.

ENDIF.

ENDLOOP.

DELETE gt_mseg WHERE compr <> 'SAME'.

0
Bertrand DELVALLEE Sep 12 at 03:52 PM
0

Hello,

It depends mostly of numbers of entries involved.

What is the size of gt_mara?

How many entries expected in MSEG for 1 entry in gt_mara?

If these 2 numbers are relatively small you can do a LOOP on gt_mara and SELECT (needed_fields) from MSEG where (cond) ORDER BY BUDAT descendig UP TO 2 ENTRIES. If your condition fill (with operator EQ) all keys of a MSEG activated index it will be as fast as possible. If this index doesn't exist you can create it in SE11.

Best regards

Bertrand

Show 2 Share
10 |10000 characters needed characters left characters exceeded

SELECT in the LOOP? That's some sort of ABAP blasphemy...

2

If you only pick 4 words in my sentences you can also blame for writing "LOOP descending create index". Which doesn't make more sense. :)

2