This post is our little collection of usefull table expressions used in SAP Transportation Management context. The examples are doamin specific, but maybe other can use the principles as well.
Special thanks to horst.keller for helping us getting started!
A simple one to get started:
Extract the latest timestamp from a table
lr_tor_a_checkin->arrival = REDUCE /scmtms/actual_date( INIT res = lr_tor_a_checkin->arrival
FOR <fs_exec> IN lt_d_execinfo
NEXT res = nmax( val1 = res
val2 = <fs_exec>-actual_date ) ).
Now for something a bit mor complex...
Extract Ealiest and latest timestamp out of a table with multiple time stamp fields in it
TYPES: BEGIN OF ty_min_max_times,
min_time TYPE /scmtms/timestamp,
max_time TYPE /scmtms/timestamp,
END OF ty_min_max_times.
CONSTANTS: lc_max TYPE timestamp VALUE '29993112000000'.
ls_min_max_times = REDUCE ty_min_max_times(
INIT res = VALUE ty_min_max_times( min_time = lc_max
max_time = 0 )
FOR <fs_stop> IN it_stop
USING KEY parent_key
WHERE ( parent_key = is_root-key )
NEXT
res-max_time = nmax( val1 = res-max_time
val2 = <fs_stop>-appointment_end
val3 = <fs_stop>-aggr_assgn_end_l
val4 = <fs_stop>-aggr_assgn_end_c
val5 = <fs_stop>-req_end
val6 = <fs_stop>-plan_trans_time )
res-min_time = nmin( val1 = res-min_time
val2 = COND timestamp( WHEN <fs_stop>-appointment_start IS NOT INITIAL THEN <fs_stop>-appointment_start ELSE lc_max )
val3 = COND timestamp( WHEN <fs_stop>-req_start IS NOT INITIAL THEN <fs_stop>-req_start ELSE lc_max )
val4 = COND timestamp( WHEN <fs_stop>-plan_trans_time IS NOT INITIAL THEN <fs_stop>-plan_trans_time ELSE lc_max )
val5 = COND timestamp( WHEN <fs_stop>-aggr_assgn_start_l IS NOT INITIAL THEN <fs_stop>-aggr_assgn_start_l ELSE lc_max )
) ).
The underlying discussion can be found here.
Now, something more than just timestamps...
Extract the shortest duration together with another value which should come from the line with the shortest duration
DATA(ls_min_dur) = REDUCE ty_min_dur( INIT result = VALUE ty_min_dur( min_dur =
99999 )
FOR <ls_lddd> IN mt_lddd
USING KEY loc_fr WHERE ( loc_fr = iv_loc_key )
NEXT
result-loc_fr = <ls_lddd>-loc_fr
result-min_dur
= nmin( val1 = result-min_dur val2 = <ls_lddd>-duration )
result-loc_to = COND #( WHEN result-min_dur = <ls_lddd>-duration
THEN <ls_lddd>-loc_to ELSE result-loc_to ) ).
