Ask whether related data exists

The dispatch team wants orders with at least one delay event. It needs the order identifiers, not every event row. EXISTS expresses the question directly.

SELECT FROM zi_dispatch_order AS orders
  FIELDS orders~dispatchorder
  WHERE EXISTS (
    SELECT FROM zi_scan_event AS scans
      FIELDS scans~dispatchorder
      WHERE scans~dispatchorder = orders~dispatchorder
        AND scans~eventtype = 'DELAY'
  )
  INTO TABLE @DATA(delayed_orders).

The inner query is correlated because it compares its event key with the current outer order. The database can stop needing duplicate event details in the result; each qualifying order is selected based on existence.

EXISTS or JOIN?

Use a join when columns from both sources belong in the result. Use EXISTS when related rows only determine whether the outer row qualifies. A join can multiply an order when several matching events exist; EXISTS avoids that shape change.

NOT EXISTS can express “no related row exists,” but confirm null and business semantics before using it. Subquery support and restrictions vary by ABAP SQL release.

Practice

Complete the correlated EXISTS predicate, select the order ID, collect rows, and set result to the count. The browser does not execute this query.

Sources

• SAP Samples, ABAP SQL: subqueries and SQL conditions
• SAP Help Portal, ABAP SQL

Version note: Check the target release for supported subquery positions and syntax.

SELECT FROM zi_dispatch_order AS orders
  FIELDS orders~dispatchorder
  WHERE EXISTS (
    SELECT FROM zi_scan_event AS scans
      FIELDS scans~dispatchorder
      WHERE scans~dispatchorder = orders~dispatchorder
        AND scans~eventtype = 'DELAY'
  )
  INTO TABLE @DATA(delayed_orders).
DATA(result) = lines( delayed_orders ).

WRITE result.

out->write( result ).