I want to get the latest data from a table in ABAP.
Here an example from the table ckmlcr:
| MANDT | KALNR | BDATJ | POPER | UNTPER | CURTP | PEINH | VPRSV | STPRS | PVPRS | WAERS | ... |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 100 | 000100000000 | 2020 | 007 | 000 | 10 | 1 | S | 1.00 | 0.00 | JPY | ... |
| 100 | 000100000000 | 2020 | 007 | 000 | 30 | 1 | S | 1.00 | 0.00 | JPY | ... |
| 100 | 000100000000 | 2020 | 007 | 000 | 31 | 1 | S | 1.00 | 0.00 | JPY | ... |
| 100 | 000100000000 | 2020 | 008 | 000 | 10 | 1 | S | 1.00 | 0.00 | JPY | ... |
| 100 | 000100000000 | 2020 | 008 | 000 | 30 | 1 | S | 1.00 | 0.00 | JPY | ... |
| 100 | 000100000000 | 2020 | 008 | 000 | 31 | 1 | S | 1.00 | 0.00 | JPY | ... |
| 100 | 000199999999 | 2020 | 007 | 000 | 10 | 1 | S | 20.00 | 0.00 | EUR | ... |
| 100 | 000199999999 | 2020 | 007 | 000 | 30 | 1 | S | 25.00 | 0.00 | EUR | ... |
| 100 | 000199999999 | 2020 | 007 | 000 | 31 | 1 | S | 20.00 | 0.00 | EUR | ... |
I want to get the latest data for each KALNR so this would mean my output table should have following values:
| MANDT | KALNR | BDATJ | POPER | UNTPER | CURTP | PEINH | VPRSV | STPRS | PVPRS | WAERS | ... |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 100 | 000100000000 | 2020 | 008 | 000 | 10 | 1 | S | 1.00 | 0.00 | JPY | ... |
| 100 | 000100000000 | 2020 | 008 | 000 | 30 | 1 | S | 1.00 | 0.00 | JPY | ... |
| 100 | 000100000000 | 2020 | 008 | 000 | 31 | 1 | S | 1.00 | 0.00 | JPY | ... |
| 100 | 000199999999 | 2020 | 007 | 000 | 10 | 1 | S | 20.00 | 0.00 | EUR | ... |
| 100 | 000199999999 | 2020 | 007 | 000 | 30 | 1 | S | 25.00 | 0.00 | EUR | ... |
| 100 | 000199999999 | 2020 | 007 | 000 | 31 | 1 | S | 20.00 | 0.00 | EUR | ... |
My program should have as selection the year
PARAMETERS: bdatj TYPE ckmlcr-bdatj DEFAULT sy-datum+0(4) OBLIGATORY.
and should uses the highest period (POPER) for each cost estimate number (KALNR).
What is the easiest way to achieve this? Due to a lot of data it would be nice to directly get the filtered data within the SQL select on the table.
This would be the SQL statement without any modifications to get the latest data.
SELECT * FROM ckmlcr INTO TABLE @DATA(ckmlcr_single)
WHERE kalnr = @<ckmlcr_line>-kalnr
AND bdatj = @bdatj.
Learn how to use subqueries
SELECT kalnr, bdatj, poper, untper, curtp, peinh, vprsv, stprs, pvprs, waers
FROM ckmlcr AS cr
INTO TABLE @DATA(ckmlcr_single)
WHERE bdatj = @bdatj
AND poper = ( SELECT MAX( poper ) from ckmlcr WHERE kalnr = cr~kalnr AND bdatj = cr~bdatj ).
P.S. Habituate yourself to put select fields explicitly instead of asterisk, it will serve a good job in future.