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

WITH, Common Table Expressions

This example demonstrates how common table expressions are used in the statement WITH.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA carrid TYPE spfli-carrid VALUE 'LH'.
    cl_demo_input=>request( CHANGING field = carrid ).
    carrid = to_upper( carrid ).

    WITH
      +conns AS (
        SELECT carrname, connid, cityfrom, cityto
              FROM spfli
                JOIN scarr ON spfli~carrid = scarr~carrid
              WHERE spfli~carrid = @carrid ),
      +cnts AS (
        SELECT COUNT(*) AS cnt
               FROM +conns )
      SELECT *
             FROM +cnts
               CROSS JOIN +conns
             ORDER BY carrname, connid
             INTO CORRESPONDING FIELDS of TABLE @itab.

    cl_demo_output=>display( itab ).

Description

A common table expression +conns creates a table of flight connections. A further expression, +cnts, determines the number of entries in the results set of +conns. The closing main query combines the results sets of both common table expressions using a cross join. This makes available the number of entries in every row of the results set of the WITH statement. The common table expression +conns is used twice in the WITH statement.