Skip to Content
Author's profile photo Horst Keller

ABAP News for Release 7.51 – Common Table Expressions (CTE) in Open SQL

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:

      +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

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).

  +cities AS ( 
    SELECT cityfrom AS city 
           FROM spfli 
           WHERE carrid = @carrid 
    SELECT cityto AS city 
           FROM spfli 
           WHERE carrid = @carrid ) 
         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:

  +cte1[( name1, name2, ... )] AS ( SELECT subquery_clauses ... ), 
  +cte2[( name1, name2, ... )] AS ( SELECT subquery_clauses ... ), 
  SELECT mainquery_clauses 
         INTO ...

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.

  +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 ) 
         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.


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      It's finally here! 🙂

      Can we expect recursion in the foreseeable future? (yes, give them a finger, and they'll take the whole hand)

      From my POV the CTEs' (well, rCTEs actually) biggest appeal is easy hierarchy traversal ... pretty please?

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      Depends on what is the "foreseeable future". ?

      It is under consideration but will take "some" time.

      As always, there is the challenge of platform independent behavior.

      Author's profile photo shivam shukla
      shivam shukla

      Can i execute the above Syntax in my ABAP editor or Do i need to write them using ADT.


      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      Since it is Open SQL, you can code it in the ABAP editor ofSAP GUI as well as of ADT.

      Author's profile photo Jan Trojan
      Jan Trojan

      As you are mentioning ADT... How about using CTEs (or even subselects) in the "SQL Console" (in Eclipse)? For me it doesn't seem to work yet.

      Rational: I want to quickly analyse/debug data, e.g. from tables & CDS-views on Netweaver layer (not on DB layer, where i could just write SQLScript).

      Author's profile photo Premal Mistry
      Premal Mistry

      Did you find an answer or workaround for this ?

      I am trying CTE/Sub-query in SQL console in Eclipse. We need to use OpenSQL and nothing seem to be working.

      Temporary tables in SQL console isn't working. I could do this in HANA Studio using direct sql qeries(native sql) !!

      Author's profile photo Vinodkumar Tarlana
      Vinodkumar Tarlana

      Hi keller,


      How to debug CTE. I tried in ABAP Editor and HANA Studio by switching on Step Size. Not able to debug individual select queries.

      Author's profile photo Amol Samte
      Amol Samte

      I have also tried to debug but individually you cant debug, execute one by one query i.e. comment all query execute first if no issue then uncomment second query ...... like that




      Author's profile photo Pavel Astashonok
      Pavel Astashonok

      How to understand

      Up to now a CTE cannot use itself as a data source.

      In the above examples we see queries like

      +cnts AS (
              SELECT COUNT(*) AS cnt
                     FROM +conns )

      where both +cnts and +conns are subqueries. What did you mean by that?

      Author's profile photo Satish Kumar Madala
      Satish Kumar Madala

      In the example you gave +cnts is referring cnt, but not +cnts itself. Hope it is clear 🙂

      Author's profile photo Pavel Astashonok
      Pavel Astashonok

      giving both datasource and CTE expression of that datasource the same name is the same as redefine the object (like overloading methods), of course it is obvious that redefinition is impossible. The above phrase is simply misleading and makes readers think that one cannot specify any CTE as datasource

      Author's profile photo Sandra Rossi
      Sandra Rossi

      I understand from the sentence that you can't do:

      +cnts AS ( SELECT ... FROM +cnts ... )

      But I agree it's obscure. A clarification would be great.

      Maybe there was an internal discussion about handling hierarchy tables, to traverse parent-child relationships in one SELECT, as can be done in CDS and in meshes...


      Author's profile photo Shiven Ganguly
      Shiven Ganguly

      Hi H.K.

      Always love to read your post.

      Just wondering which one would be faster in terms of performance, nested subqueries or CTE ?Currently in my case involving approx 5-6 tables the time taken is same but CTE pleases the eye in terms of understanding?


      Author's profile photo Johann Fößleitner
      Johann Fößleitner


      you wrote "... Each WITH statement must be completed by a mainquery that uses at least one of its CTEs as a data source ..."

      I think this is a bit confusing described, because you can use a CTE also in a subquery only. Like in the following "Hello World" Example:

        +people( partner, bpkind ) as ( select partner, bpkind from but000 where type = '1' )
        select * from but050 where partner2 in ( select partner from +people where bpkind = @space )
        into table @data(result2).

      Regards, Johann