Blog > Dynamic Common Table Expressions in Open SQL

Dynamic Common Table Expressions in Open SQL

Katarzyna Wydro SAP Integration Consultant
icon__calendar 2021-02-11

In this article you will learn:

  • What are Common Table Expressions and why are they useful
  • How to use Common Table Expressions dynamically

Reading time: 4 minutes



As of ABAP Release 7.51 Common Table Expressions (CTE) are available in Open SQL. They allow to encapsulate the SQL query and reuse result recursively as a data source in the main query or in another CTE within one statement.

In this article I’ll illustrate how to use CTEs using an example of dynamic query which counts records from one table joined with second table used to limit the result set.

Why bother?

CTEs enable recurrency in SQL, meaning that temporary result of CTE is reused in another CTE or in the main query defined within same statement.

Sometimes you may be limited by the Open SQL capabilities. Using CTEs, for instance, you can use SELECT from <subquery> in Open SQL.

In my case I needed to execute dynamic SELECT count( DISTINCT col1, col2, …) which is not possible in the regular OpenSQL. I was able to do this using dynamic Common Table Expression, which I’ll explain in details in the further section of this blog.



Common Table Expressions in OpenSQL are defined using the statement WITH. The syntax is shown below:

WITH statement defines the subquery to which you refer within current statement.  You cannot refer to CTE in another WITH or SELECT statement.

Name of the CTE definition always starts with the prefix “+”.  Optionally you can also define aliases of result columns. You may define multiple expressions in a single WITH statement.

The main query may be either SELECT statement or the cursor.

Dynamic CTE Case Study

Some scenarios require generic approach, where you define the query in a dynamic way. The example shows a query which counts number of distinct records of one table selected according to conditions based on another table.

First, let’s see the OpenSQL query:

The example below shows how the query would look like during execution:

In order to obtain the desired result only DISTINCT records have to be selected. And you need to use all the primary key fields of table1 to get the proper result.

Now, let’s look in more details at the dynamic Common Table Expression example:

The line below is used to store CTE name, which will be specified in the main query dynamically.

CTE is defined as distinct selection of fields specified in the variable lv_select. Data source of this subquery is specified in lv_join_cond, which in this scenario is a joining condition of dynamically specified tables. The WHERE-clause is a table consisting of selection conditions referring to joined tables.

Next part of the WITH statement is a main query which selects the data from subquery defined in the CTE expression. The variable lv_cte_name contains a name of the expression which is used as a data source. It must be specified dynamically since the CTE declaration part is not static.



In this blog I’ve shown you what are Common Table Expressions, why and how to use them in a generic way. I hope you enjoyed! If you want to learn more please check our blogs

If you want to find out more about the Int4 IFTT features, take a look at rest blogs about it, also you can book a consultation with the product demo or contact us.


Read also:

1. About COALESCE, Left outer join, NULL and the link between them

2. Discover approval level in MM Flexible Workflow agent determination BAdI

Katarzyna Wydro SAP Integration Consultant
SAP consultant since 2016. Involved in local and international projects in developer role. Certified in ABAP and SAP Cloud Platform Integration area.