Dynamic Common Table Expressions in Open SQL

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

Introduction

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.

Syntax

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

WITH

  +cte1[( name1, name2, ... )] AS ( SELECT subquery_clauses [UNION ...] )[,

  +cte2[( name1, name2, ... )] AS ( SELECT subquery_clauses [UNION ...] ),

  ... ]

  SELECT mainquery_clauses

FROM +cte1

...

INTO

... .

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:

lv_cte_name = '+subquery'.

WITH +subquery AS ( SELECT DISTINCT (lv_select)

          FROM (lv_join_cond)

           WHERE (iit_where) )

      SELECT COUNT(*) FROM (lv_cte_name)

        INTO @lv_count.

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

SELECT count( DISTINCT table1.keyfield1, table2.keyfield2, table3.keyfield3...) "number of distinct records based on table1's key fields

FROM table1 left outer join table2  "join to table2

ON table1.field1 = table2.field1

AND table1.field2 = table2.field2

WHERE table2.field3 IN (...) and table2.field3 is not null. "condition referring to table2 - record in table1 must have a match in table2 and matched record(s) must belong to specific range

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.

lv_cte_name = '+subquery'.

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.

WITH +subquery AS ( SELECT DISTINCT (lv_select)

          FROM (lv_join_cond) CLIENT SPECIFIED

           WHERE (iit_where) )

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.

SELECT COUNT(*)  FROM  (lv_cte_name) 

        INTO @lv_count.

Summary

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