Collection of our best table expressions

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 ) ).