The keyword WITH in ABAP SQL introduces a common table expressions (CTE) which bypasses table buffering. The common table expression defined in the WITH statement can be used after its definition in the subquery of another common table expression and in the main query as the data source data_source.
A common table expression creates a temporary tabular result set, which can be accessed during execution of the WITH statement. Common table expressions can therefore be considered temporary views, which only exist for the duration of the database access.
Common table expressions can be used whenever intermediate results are required in a SELECT statement. ABAP SQL statements are more readable if common table expressions are used, and a common table expression is always useful if intermediate results are required more than once.
Using common table expressions as a data source also provides the option of selecting directly from a subquery SELECT FROM subquery, which is not possible in ABAP SQL.
Here is a small use case of WITH –
Now slightly changing the CTE where it contains all fields from SCARR, the subquery is adjusted to select CARRID from the CTE.