Here's a sample of the data I wish to select from in the AFRU
table.
+----------------------+-----------------+-------------------------+
| Confirmation (RUECK) | Counter (RMZHL) | Finish Execution (IEDD) |
+----------------------+-----------------+-------------------------+
| 30116 | 1 | 08/26/2014 |
| 30116 | 2 | 08/27/2014 |
| 30116 | 3 | 09/27/2013 |
+----------------------+-----------------+-------------------------+
Given this data set, I'd like to select the last row because it contains highest RMZHL
value (I'm really only after the IEDD
value of that record). Here's the code I've been trying to use:
SELECT MAX( rmzhl ), iedd FROM afru
INTO (@DATA(lv_rmzhl), @DATA(lv_iedd))
WHERE rueck = '0000030116'
GROUP BY rmzhl, iedd.
...
<some fancy code here>
...
ENDSELECT.
However, this selection always nets me the values 1
for lv_rmzhl
, and 20140826
for lv_iedd
, therefore not getting the MAX
value like I'm attempting to get. I understand that his could be easily accomplished by putting it all into an internal table and sorting on the RMZHL
field, but I'm looking for a more proper way.
You could do a subselect like:
SELECT SINGLE iedd
FROM afru
INTO @DATA(lv_iedd)
WHERE rueck = '0000030116'
AND rmzhl = ( SELECT MAX( rmzhl ) FROM afru WHERE rueck = '0000030116' ).
I've used this in a lot of cases even in combination with FOR ALL ENTRIES
and the performance difference is very low.