When I
mentioned CTEs one year ago, the reaction was that I'm "
teasing". But well, here we are. The Open SQL boys are really a hard-working bunch.
The
wish was to have a
SELECT ... FROM subquery in Open SQL. That's one thing, you can use CTEs for:
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.
The new SQL keyword
WITH introduces the definition of one or more CTEs. Each CTE is a kind of subquery that produces a tabular result that can be used as a data source in all subsequent queries of the same
WITH statement. In the example, a general table expression
+conns (where the + is a mandatory prefix of the name) creates a table of flight connections. A further expression,
+cnts, determines the number of entries in the result set of
+conns. The closing mainquery combines the result sets of both general table expressions using a cross join (also new in ABAP 7.51).
WITH
+cities AS (
SELECT cityfrom AS city
FROM spfli
WHERE carrid = @carrid
UNION DISTINCT
SELECT cityto AS city
FROM spfli
WHERE carrid = @carrid )
SELECT *
FROM sgeocity
WHERE city IN ( SELECT city
FROM +cities )
INTO TABLE @DATA(result).
In this example, there is one CTE
+cities defining a subquery including a
UNION. The result set of the CTE is evaluated as the data source of the subquery of the
WHERE condition of the main query of the
WITH statement.
The general CTE syntax is as follows:
WITH
+cte1[( name1, name2, ... )] AS ( SELECT subquery_clauses ... ),
+cte2[( name1, name2, ... )] AS ( SELECT subquery_clauses ... ),
...
SELECT mainquery_clauses
...
INTO ...
...
[ENDWITH].
Each
WITH statement must be completed by a mainquery that uses at least one of its CTEs as a data source and each CTE must be used in at least one other subsequent query. Up to now a CTE cannot use itself as a data source.
WITH can be used as a standalone statement or after the statement
OPEN CURSOR.
The
ENDWITH is an ABAP specialty that fulfills the same task as
ENDSELECT. If you don't write in to an internal table behind
INTO of the main query, you open a
WITH loop that must be closed with
ENDWITH.
With
( name1, name2, ... ) you can redefine the column names of the tabular result sets of a CTE, e.g.
WITH
+connections AS (
SELECT spfli~carrid, carrname, connid, cityfrom, cityto
FROM spfli
INNER JOIN scarr
ON scarr~carrid = spfli~carrid
WHERE spfli~carrid BETWEEN @from_id AND @to_id ),
+sum_seats AS (
SELECT carrid, connid, SUM( seatsocc ) AS sum_seats
FROM sflight
WHERE carrid BETWEEN @from_id AND @to_id
GROUP BY carrid, connid ),
+result( name, connection, departure, arrival, occupied ) AS (
SELECT carrname, c~connid, cityfrom, cityto, sum_seats
FROM +connections AS c
INNER JOIN +sum_seats AS s
ON c~carrid = s~carrid AND
c~connid = s~connid )
SELECT *
FROM +result
ORDER BY name, connection
INTO TABLE @DATA(result).
The result sets of both general table expressions
+connections and
+sum_seats are merged in the subquery of the general table expression
+result in a join expression. An explicit name list assigns names to the resulting columns. These names are used in the mainquery to sort the results.
With the introduction of
WITH, the standalone statement
SELECT suddenly lost a bit of its peculiarity. In fact it can be seen as a special case of a mainquery without
WITH. Therefore the terminology of queries was generalized in Open SQL: Each language element
SELECT implements a query and we distinguish between mainqueries and subqueries.
- A mainquery is either a standalone SELECT statement, the final query of a WITH statement, or the final query of an OPEN CURSOR statement.
- A subquery is possible in a WHERE condition, after WITH for the definition of a CTE, or as a data source of an INSERT statement.
All queries can be combined with
UNION and the most clauses are common for all queries except the ABAP specific INTO that can be used for the mainqueries of a standalone
WITH and a standalone
SELECT and behind a
FETCH only.
For more information see
WITH.
