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

SELECT, Union with Aggregate Expression

This example demonstrates how a union is created across a table using an aggregate expression.

Other versions: 7.31 | 7.40 | 7.54

Source Code

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

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

    cl_demo_output=>display( result ).

Description

UNION is used to create the union of the results sets of two SELECT statements for the same database table. The first SELECT statement reads all flights for a carrier and the second SELECT statement aggregates the flights by connection (adding the number of occupied seats). A CAST expression is used to insert a column for the flight date fldate (from the first results set) with a suitable data type in the second results set. A comment indicates how this is also possible using a host expression in this case. A further column mark is filled with literals to make realistic sorts possible. The full result contains the aggregated rows under the associated rows of the first results set and are flagged with "X".