CTE – Common Table Expressions



ABAP HANA 7.51: CTE – Common Table Expressions

CTE – Common Table Expression is one of the revolutionary concepts in ABAP SQL where it is very simple to push down multiple chained SQL queries into one and hit Database once. With Introduction of CTE, developers should refrain from the practice of writing SQL followed by sy-subrc check and then another query inside sy-subrc condition based on the result of the first query.

CTEs can be deep and long. Result of one fetch is stored in + and this can be
subsequently used further part of CTE as if it were a Transparent table

CTE is an extension of Open SQL and Subquery mechanism where multiple queries can be joined using WITH keyword along with + and last part of Query works for result consolidation. Some important point regarding CTE is as follows:

  • Each Query part name of CTE(name starts with +) should be unique
  • UNION DISTINCT/ALL can be used
  •  Each CTE expression is separated by a comma
  • AS keyword is followed by a subquery. SELECT from SUBQUERY can also be used in the context of CTE.
  • CTE syntax which starts by WITH keyword can be closed by an ENDWITH, however, it will have the same effect as SELECT-ENDSELECT
  • CTE and buffering doesn’t go together
  • Keyword CLIENT_SPECIFIED can be used to override default client handling

CTE should be considered parallelly with enhanced Open SQL, CDS, and AMDP for “when to use” checklist while creating a coding guideline for any S4 HANA project.

Sample Source Code:

* CTE Code 



+customer AS ( Part 1 – Fetching Customer data

          SELECT DISTINCT b~matnr, b~werks, b~charg, b~sobkz, b~kunnr, b~kulab

          FROM kna1 AS a

          INNER JOIN msku AS b

          ON a~kunnr = b~kunnr


                 a~ktokd = ‘ZTST’

                 AND   b~werks = ‘P001’

                 AND b~kulab GT 0



+marc AS ( Part 2 – Fetching Material Plant Data

          SELECT matnr, werks, sernp

          FROM marc

          WHERE matnr IN ( SELECT
matnr FROM +customer )

werks IN ( SELECT DISTINCT werks FROM +customer )

sernp NE @space



+mch1 AS ( Part 3 – Fetching Batch Data

          SELECT matnr, charg, vfdat

          FROM mch1

          WHERE matnr IN ( SELECT
matnr FROM +customer )

charg IN ( SELECT DISTINCT charg FROM +customer )



+equi AS ( Part 4 – Fetching Equipment data

          SELECT c~equnr, c~matnr, c~sernr

          FROM equi AS c

          INNER JOIN eqbs AS d

          ON c~equnr = d~equnr

          WHERE c~matnr IN ( SELECT DISTINCT matnr FROM +customer )

          AND d~b_werk IN ( SELECT DISTINCT werks FROM +customer )

          AND d~b_charge IN ( SELECT DISTINCT charg FROM +customer )

          AND d~sobkz IN ( SELECT DISTINCT sobkz FROM +customer )

          AND d~kunnr IN ( SELECT DISTINCT kunnr FROM +customer )



+result AS ( Part 5 – combining above data

            SELECT cu~matnr, cu~werks, cu~charg, cu~sobkz, cu~kunnr, cu~kulab,

            ma~sernp, mc~vfdat, eq~sernr

            FROM +customer AS cu

            LEFT OUTER JOIN +marc AS ma ON cu~matnr = ma~matnr AND cu~werks = ma~werks

            LEFT OUTER JOIN +mch1 AS mc ON cu~matnr = mc~matnr AND cu~charg = mc~charg

            LEFT OUTER JOIN +equi AS eq ON cu~matnr = eq~matnr



            SELECT * ” Part 6 – Preparing result

            FROM +result

            ORDER BY matnr, werks, kunnr, sernr

            INTO TABLE @DATA(lt_res).


  1. Part 1: Fetches Customer data and joins two tables
  2. Part 2: Fetches Plant Material data
  3. Part 3: Gets Batch data
  4. Part 4: Gets Equipment data
  5. Part 5: Combines Part 1-4 using LEFT OUTER joins
  6. Part 6: Prepares result to be further used in ABAP logic

Take away:

  1. CTE is long here to merge multiple queries
  2. One CTE hits database once hence performance can be expected better



The output gives the result as a filtered data in internal table lt_res.



2 thoughts on “CTE – Common Table Expressions

Leave a Reply