CTE – Common Table Expressions

Index

 

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
DISTINCT
matnr 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
DISTINCT
matnr 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:

  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

 

Output:

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

Index

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s