ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  WITH →  WITH Examples 

WITH, Aggregation for Join Set

This example demonstrates how to use WITH for an aggregation.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA carrid TYPE sflight-carrid VALUE 'AA'.
    cl_demo_input=>request( CHANGING field = carrid ).

    WITH +agg AS (
      SELECT carrid,
             connid,
             CAST( '00000000' AS DATS ) AS fldate,
             SUM( seatsocc ) AS seatsocc
             FROM sflight
             WHERE carrid = @( to_upper( carrid ) )
             GROUP BY carrid, connid )
      SELECT ' ' AS mark, carrid, connid, fldate, seatsocc
             FROM sflight
             WHERE carrid = @( to_upper( carrid ) )
             UNION SELECT 'X' AS mark,
                          carrid, connid, fldate, seatsocc
                          FROM +agg
             ORDER BY carrid, connid, mark, fldate, seatsocc
             INTO TABLE @DATA(result).

    cl_demo_output=>display( result ).

Description

This example has the same result as the executable example for a union with a global temporary table. The source code is almost identical. Here the global temporary table demo_sflight_agg has been replaced by a common table expression +agg and both SELECT statements are part of a WITH statement.