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
WITH
+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
WHERE
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
DISTINCTmatnr FROM +customer )
AND
werks IN ( SELECT DISTINCT werks FROM +customer )
AND
sernp NE @space
),
+mch1 AS ( “ Part 3 – Fetching Batch Data
SELECT matnr, charg, vfdat
FROM mch1
WHERE matnr IN ( SELECT
DISTINCTmatnr FROM +customer )
AND
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).
Illustration:
- Part 1: Fetches Customer data and joins two tables
- Part 2: Fetches Plant Material data
- Part 3: Gets Batch data
- Part 4: Gets Equipment data
- Part 5: Combines Part 1-4 using LEFT OUTER joins
- Part 6: Prepares result to be further used in ABAP logic
Take away:
- CTE is long here to merge multiple queries
- One CTE hits database once hence performance can be expected better
Output:
The output gives the result as a filtered data in internal table lt_res.
How to see entries in sub queries in debugging mode.
you cant yet