Keep the order even when no event matches

Some parcels have not been scanned yet. An inner join would discard those orders. A left outer join preserves each left-side order and supplies matching scan information when it exists.

Write the join and its matching condition

SELECT FROM zi_dispatch_order AS orders
  LEFT OUTER JOIN zi_scan_event AS scans
    ON orders~dispatchorder = scans~dispatchorder
  FIELDS orders~dispatchorder, scans~scannedat
  INTO TABLE @DATA(order_scans).

Rows with no event have no matching value on the right side. SQL models that missing value as NULL; an ABAP target may represent it as an initial value unless null indicators are requested. Therefore, a blank timestamp alone may not tell you whether the source was absent or the actual field was blank.

Handle missing values explicitly

ABAP SQL supports null-handling features such as null indicators and, for suitable expressions/releases, COALESCE. Choose a representation that preserves the distinction your business rule needs. Verify availability and syntax for your release, especially before using IS NULL patterns or moving nullable values into ABAP targets.

A common join-filter trap

Adding a condition on the right-side table in WHERE can discard rows with no match, effectively changing the intended outer-join behavior. Put relationship matching in ON; decide carefully whether additional filters belong in ON or WHERE.

Practice

Complete the left join and count result rows. The checker verifies structure only and does not model nulls or execute SQL.

Sources

• SAP Samples, ABAP SQL: combining data sources and null expressions
• SAP Samples, CDS View Entities: joins and COALESCE
• SAP Help Portal, ABAP SQL

Version note: Null indicators and SQL expression support depend on the ABAP release and query source.

SELECT FROM zi_dispatch_order AS orders
  LEFT OUTER JOIN zi_scan_event AS scans
    ON orders~dispatchorder = scans~dispatchorder
  FIELDS orders~dispatchorder, scans~scannedat
  INTO TABLE @DATA(order_scans).
DATA(result) = lines( order_scans ).

WRITE result.

out->write( result ).