Get the newest partner record for each NAST table message type?

Question

The question is generic (not anymore after the edits...), because I understand this is a common problem for other tables as well, but I will describe my particular problem with the selection of partners for output messages.

For a given invoice, I want to get the partner linked to each message type in NAST table. There could be multiple entries for the same message type so I want the newest one based on fields ERDAT and ERUHR (date and time).

I tried to do it with subqueries, but it got very ugly, especially the time field requires a double subquery because you first need to get the latest date...

Then I implemented this solution but I don't like it and I was hoping for something better

DATA: lt_msg_type_rg TYPE RANGE OF kschl.

lt_msg_type_rg = VALUE #( FOR ls_msg_type IN me->mt_message_type 
                          ( sign = 'I' option = 'EQ' low = ls_msg_type-kschl ) ).
SELECT FROM nast AS invoice_msg_status
      FIELDS invoice_msg_status~kschl AS message_type,
             invoice_msg_status~parnr AS partner_num,
             CONCAT( invoice_msg_status~erdat, invoice_msg_status~eruhr ) AS create_timestamp
      WHERE invoice_msg_status~kappl  = @c_app_invoicing
        AND invoice_msg_status~objky  = @me->m_invoice_num
        AND invoice_msg_status~kschl IN @lt_msg_type_rg
      ORDER BY create_timestamp DESCENDING
      INTO TABLE @DATA(lt_msg_partner).

DATA: lt_partner_rg TYPE RANGE OF parnr.

LOOP AT lt_msg_partner ASSIGNING FIELD-SYMBOL(<lgr_msg_partner>) GROUP BY <lgr_msg_partner>-message_type.
  lt_partner_rg = COND #( WHEN line_exists( lt_partner_rg[ low = <lgr_msg_partner>-partner_num ] )
                          THEN lt_partner_rg
                          ELSE VALUE #( BASE lt_partner_rg ( sign = 'I' option = 'EQ' low = <lgr_msg_partner>-partner_num ) ) ).
ENDLOOP.

Example input (skipped irrelevant fields)

+-------+-------+-------+-------+------------+-------+
| KAPPL | OBJKY | KSCHL | PARNR |   ERDAT    | ERUHR |
+-------+-------+-------+-------+------------+-------+
| V3    | 12345 | Z001  |    11 | 27.10.2020 | 11:00 |
| V3    | 12345 | Z001  |    12 | 27.10.2020 | 12:00 |
| V3    | 12345 | Z002  |    13 | 27.10.2020 | 11:00 |
+-------+-------+-------+-------+------------+-------+

Expected output:

[12]
[13]

Answer 1

As suggested in the comments to the AMDP-variant answer, this can also be done with CDS views.

First, we need a view that timestamps the data:

@AbapCatalog.sqlViewName: 'timednast'
define view timestamped_nast as select from nast {
    kappl,
    objky,
    kschl,
    parnr,
    concat(erdat, eruhr) as timestamp
}

Second, because CDS' syntax doesn't allow timestamping and grouping in a single view, we need another view that calculates the latest change dates for each message type:

@AbapCatalog.sqlViewName: 'lchgnast'
define view last_changed_nast as
select from timestamped_nast {
    kappl,
    objky,
    kschl,
    max(timestamp) as last_changed_on
} group by kappl, objky, kschl

Third, we need to select the partner numbers associated with these time points:

@AbapCatalog.sqlViewName: 'lchbnast'
define view last_changers_nast as
  select from last_changed_nast
  inner join timestamped_nast 
    on timestamped_nast.kappl = last_changed_nast.kappl
    and timestamped_nast.objky = last_changed_nast.objky
    and timestamped_nast.kschl = last_changed_nast.kschl
    and timestamped_nast.timestamp = last_changed_nast.last_changed_on
{
    timestamped_nast.kappl,
    timestamped_nast.objky,
    timestamped_nast.kschl,
    parnr
}

A SELECT on the last view last_changers_nast, including the selection criteria on kappl, objky, and kschl will then produce the list of latest changers.

I am not sure about the keys of the nast table. The third view assumes that there will be no two entries with exactly identical timestamps for one object. If this isn't true, the third view should add another aggregation by using max(parnr) instead of parnr

Answer 2

Unfortunately, SQL does not provide a simple syntax for this rather common kind of selection. Solutions will always involve multiple subsequent or nested selects.

According to your description, I assume you already found the do-it-all-in-a-single-deeply-nested ABAP SQL statement, but you are not satisfied with it because readability suffers too much.

For cases like this, we often resort to ABAP-Managed Database Procedures (AMDPs). They allow decomposing complicated nested selects into a series of simple subsequent selects.

CLASS cl_read_nast DEFINITION
    PUBLIC FINAL CREATE PUBLIC.

  PUBLIC SECTION.

    INTERFACES if_amdp_marker_hdb.

    TYPES:
      BEGIN OF result_row_type,
        parnr TYPE char2,
      END OF result_row_type.

    TYPES result_table_type
      TYPE STANDARD TABLE OF result_row_type
        WITH EMPTY KEY.

    TYPES:
      BEGIN OF key_range_row_type,
        kschl TYPE char4,
      END OF key_range_row_type.

    TYPES key_range_table_type
      TYPE STANDARD TABLE OF key_range_row_type
        WITH EMPTY KEY.

    CLASS-METHODS select
      IMPORTING
        VALUE(application)  TYPE char2
        VALUE(invoice_number)  TYPE char5
        VALUE(message_types)  TYPE key_range_table_type
      EXPORTING
        VALUE(result) TYPE result_table_type.

ENDCLASS.

CLASS cl_read_nast IMPLEMENTATION.

  METHOD select
      BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
      USING nast.

    last_changed_dates =
      select kappl, objky, kschl,
          max( erdat || eruhr ) as last_changed_on
        from nast
        where kappl = :application
          and objky = :invoice_number
          and kschl in
            ( select kschl from :message_types )
        group by kappl, objky, kschl;

    last_changers =
      select nast.kschl,
          max( nast.parnr ) as parnr
        from nast
        inner join :last_changed_dates
          on nast.kappl = :last_changed_dates.kappl
          and nast.objky = :last_changed_dates.objky
          and nast.kschl = :last_changed_dates.kschl
          and nast.erdat || nast.eruhr = :last_changed_dates.last_changed_on
        group by nast.kschl;

    result =
      select distinct parnr
        from :last_changers;

  ENDMETHOD.

ENDCLASS.

Verified with the following integration test:

CLASS integration_tests DEFINITION
    FOR TESTING RISK LEVEL CRITICAL DURATION SHORT.

  PRIVATE SECTION.

    TYPES db_table_type
      TYPE STANDARD TABLE OF nast
        WITH EMPTY KEY.

    CLASS-METHODS class_setup.

    METHODS select FOR TESTING.

ENDCLASS.

CLASS integration_tests IMPLEMENTATION.

  METHOD class_setup.

    DATA(sample) =
      VALUE db_table_type(
        ( kappl = 'V3' objky = '12345' kschl = 'Z001' parnr = '11' erdat = '20201027' eruhr = '1100' )
        ( kappl = 'V3' objky = '12345' kschl = 'Z001' parnr = '12' erdat = '20201027' eruhr = '1200' )
        ( kappl = 'V3' objky = '12345' kschl = 'Z002' parnr = '13' erdat = '20201027' eruhr = '1100' ) ).

    MODIFY nast
      FROM TABLE @sample.

    COMMIT WORK AND WAIT.

  ENDMETHOD.

  METHOD select.

    DATA(invoicing) = 'V3'.

    DATA(invoice_number) = '12345'.

    DATA(message_types) =
      VALUE zcl_fh_read_nast=>key_range_table_type(
        ( kschl = 'Z001' )
        ( kschl = 'Z002' ) ).

    cl_read_nast=>select(
      EXPORTING
        application = invoicing
        invoice_number = invoice_number
        message_types = message_types
      IMPORTING
        result = DATA(actual_result) ).

    DATA(expected_result) =
      VALUE cl_read_nast=>result_table_type(
        ( parnr = '12' )
        ( parnr = '13' ) ).

    cl_abap_unit_assert=>assert_equals(
        act = actual_result
        exp = expected_result ).

  ENDMETHOD.

ENDCLASS.

Answer 3

First of all, your piece is not correct, because you are checking existence (deduplication) only by partner number, and potentially the same partner could serve different message types, at least in my dataset on my test system I see such rows. So you should check by message type also. Grouping loop by message type and deduplication by partner number makes no sense, as you are stripping valid partners, which occurs in different types. You need:

SELECT 
....
ORDER BY message_type, create_timestamp DESCENDING
....

So your LOOP grouping can be simplified into these two lines:

DELETE ADJACENT DUPLICATES FROM lt_msg_partner COMPARING message_type.
lt_partner_rg = VALUE #( BASE lt_partner_rg FOR GROUPS value_no OF <line_no> IN lt_msg_partner GROUP BY ( partner_num = <line_no>-partner_num ) WITHOUT MEMBERS ( sign = 'I' option = 'EQ' low = value_no-partner_num ) ).