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

WITH - mainquery_clauses

Other versions: 7.31 | 7.40 | 7.54

Syntax

... [SINGLE [FOR UPDATE]] 

    { FROM source 

      FIELDS select_clause } 

  | { select_clause 

      FROM source } 

    [WHERE sql_cond] 

    [GROUP BY group] [
HAVING group_cond] 
  
 [ORDER BY sort_key] 

    [db_hints] ... 

Effect

Possible clauses and additions of the main query of a WITH statement. All the same clauses are possible as for a standalone a SELECT statement except for FOR ALL ENTRIES, and they have the same effect. Under the same circumstances, as in the case of the standalone SELECT statement, a SELECT loop is opened. In this case, this has to be closed with ENDWITH.

The results set of a common table expression does not have a client column, which means that the FROM clause of the main query cannot contain the addition USING for switching or the obsolete addition CLIENT SPECIFIED for disabling implicit client handling.

If an internal table @itab with elementary row type is accessed in the FROM clause of a common table expression, the SELECT list cannot be or contain data_source~.

Example

The example shows a WITH statement, whose main query creates a tabular result set. Since the example writes to an internal table itab, no SELECT loop is opened.

WITH 
  +carriers AS ( SELECT FROM scarr 
                       FIELDS carrid, carrname ) 
  SELECT FROM spfli AS s 
           INNER JOIN +carriers AS c 
             ON s~carrid = c~carrid 
         FIELDS c~carrname, s~connid 
         WHERE s~carrid = 'UA' 
         INTO TABLE @DATA(itab) 
         UP TO 10 ROWS. 

  cl_demo_output=>display( itab ).