ABAP - Common Table Expression (CTE)

Table of contents

• WITH
• Usage
• Performance
• Manual query
• Core Data Service
• Measurement
• Example
• Conclusion

The general table expression was introduced with ABAP 7.52, with which local views can be defined, via which access can then take place. The amounts of data are read efficiently on a HANA database and the data is evaluated. As an example, we use our data model from Core Data Services.

 

WITH

How are such statements put together? The compound expressions are introduced with a WITH, followed by data access, which forms a result set in the form of a table. This expression is then combined with an alias and another expression can follow a comma in order to generate a large number of data pots. At the end of the statement comes the main query, which then usually refers to the delimited quantities. Let's take a look at an example:

WITH
  +eupartners AS (
    SELECT FROM zbs_dmo_partner FIELDS partner WHERE country = 'DE'
    UNION DISTINCT
    SELECT FROM zbs_dmo_partner FIELDS partner WHERE country = 'CH'
  ),
  +partner_per_month AS (
    SELECT FROM zbs_dmo_invoice
      FIELDS DISTINCT partner, document, substring( doc_date, 1, 6 ) AS buy_month
      WHERE partner IN ( SELECT partner FROM +eupartners )
  )
  SELECT FROM +partner_per_month
    FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
    GROUP BY buy_month
    ORDER BY doc_number DESCENDING, buy_month DESCENDING
    INTO TABLE @DATA(lt_result).

 

What exactly is happening in the example?

• In the first step, we create a number of partners from Germany and Switzerland with UNION and assign them to the alias "+eupartners".
• In the second step, we select all documents for these determined partners and prepare the output fields so that, in addition to partner and document, there is also year/month to determine the document volume per month. We assign the result to the "+partner_per_month" data set.
• In the last step, the actual selection of the result takes place, we determine the number of documents per month as a result for Germany and Switzerland.

 

The result of the query now looks like this for us, although depending on the amount of data, it may look different for you:

 

Usage

In our example you have seen the different possibilities of use, in addition to various set operations such as UNION, EXCEPT or INTERSECT, table functions such as CONCAT or SUBSTRING are also possible to create the result set. Such queries are mainly used when you need temporary views in the logic and do not want to create them on the database.

There are no limits when using the queries, you can also merge the results of the queries yourself into complex statements and query or merge the data volumes generated as you wish. Only if it makes sense for the query, of course.

 

Performance

Let's take a look at the performance of the statement and compare it to a manual operation within the class and a core data service. For comparison, we speak of a small data volume of a total of 122 documents.

 

Manual query

Here is the manual query about the class, we read in all the data and work with the dataset locally. We use the possibility of a SELECT on @itab for the last query:

SELECT FROM zbs_dmo_partner
  FIELDS 'I' AS sign, 'EQ' AS option, partner AS low, partner AS high
  WHERE country IN ('CH','DE')
  INTO TABLE @DATA(lt_r_partner).

SELECT FROM zbs_dmo_invoice
  FIELDS DISTINCT partner, document, substring( doc_date, 1, 6 ) AS buy_month
  WHERE partner IN @lt_r_partner
  INTO TABLE @DATA(lt_invoice).

SELECT FROM @lt_invoice AS invoice
  FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
  GROUP BY buy_month
  ORDER BY doc_number DESCENDING, buy_month DESCENDING
  INTO TABLE @DATA(lt_result).

 

Core Data Service

Let's now map the queries as CDS views in the system. To do this, we have to create two views to summarize the basic data. The first CDS view forms the basic set of partners, also via a UNION:

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Partner from EU'
@Metadata.ignorePropagatedAnnotations: true
define view entity ZBS_I_DmoCTEPartnerEU
  as select from zbs_dmo_partner
{
  key partner
}
where
  country = 'DE'
union select from zbs_dmo_partner
{
  key partner
}
where
  country = 'CH'

 

The second view provides the documents per month for the previously delimited documents:

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Sum per Month'
@Metadata.ignorePropagatedAnnotations: true
define view entity ZBS_I_DmoCTESumPerMonth
  as select from ZBS_I_DmoCTEPartnerEU as Partner
    join         zbs_dmo_invoice       as Invoice on Invoice.partner = Partner.partner
{
  key Invoice.partner,
  key Invoice.document,
      substring( Invoice.doc_date, 1, 6 ) as buy_month
}

 

In the last step we still need the data query in our test class, which refers to the Core Data Service:

SELECT FROM ZBS_I_DmoCTESumPerMonth
  FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
  GROUP BY buy_month
  ORDER BY doc_number DESCENDING, buy_month DESCENDING
  INTO TABLE @DATA(lt_result).

 

Measurement

The data for the measurement is very manageable, so we loop the whole thing and run each case study 1000 times.

 

As you can see from the documentation, the CTE bypasses the table buffer, so we ran it at the very end so there is no dependency on buffered queries. As a result, one can roughly state that CTE does not have to hide behind the CDS views. Because the data does not have to be copied into the class, it is also a lot faster than running it manually.

Hint: The accuracy of the measurement plays no role for us in this example, the rough measurement variables alone should suffice for comparison.

 

Example

Here is the complete example for the performance comparison and the class to run again:

CLASS zcl_bs_demo_cte DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

  PROTECTED SECTION.
  PRIVATE SECTION.
    CONSTANTS:
      c_times TYPE i VALUE 1000.

    METHODS:
      select_in_code
        IMPORTING
          io_out TYPE REF TO if_oo_adt_classrun_out,

      select_with_cte
        IMPORTING
          io_out TYPE REF TO if_oo_adt_classrun_out,

      select_from_cds
        IMPORTING
          io_out TYPE REF TO if_oo_adt_classrun_out.
ENDCLASS.



CLASS zcl_bs_demo_cte IMPLEMENTATION.
  METHOD if_oo_adt_classrun~main.
    DATA:
      ld_start TYPE timestampl,
      ld_end   TYPE timestampl.

    GET TIME STAMP FIELD ld_start.
    select_from_cds( out ).
    GET TIME STAMP FIELD ld_end.
    out->write( |With CDS: { ld_end - ld_start }| ).

    GET TIME STAMP FIELD ld_start.
    select_in_code( out ).
    GET TIME STAMP FIELD ld_end.
    out->write( |With Code: { ld_end - ld_start }| ).

    GET TIME STAMP FIELD ld_start.
    select_with_cte( out ).
    GET TIME STAMP FIELD ld_end.
    out->write( |With CTE: { ld_end - ld_start }| ).
  ENDMETHOD.


  METHOD select_with_cte.
    DO c_times TIMES.
      WITH
        +eupartners AS (
          SELECT FROM zbs_dmo_partner FIELDS partner WHERE country = 'DE'
          UNION DISTINCT
          SELECT FROM zbs_dmo_partner FIELDS partner WHERE country = 'CH'
        ),
        +partner_per_month AS (
          SELECT FROM zbs_dmo_invoice
            FIELDS DISTINCT partner, document, substring( doc_date, 1, 6 ) AS buy_month
            WHERE partner IN ( SELECT partner FROM +eupartners )
        )
        SELECT FROM +partner_per_month
          FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
          GROUP BY buy_month
          ORDER BY doc_number DESCENDING, buy_month DESCENDING
          INTO TABLE @DATA(lt_result).
    ENDDO.
  ENDMETHOD.



  METHOD select_in_code.
    DO c_times TIMES.
      SELECT FROM zbs_dmo_partner
        FIELDS 'I' AS sign, 'EQ' AS option, partner AS low, partner AS high
        WHERE country IN ('CH','DE')
        INTO TABLE @DATA(lt_r_partner).

      SELECT FROM zbs_dmo_invoice
        FIELDS DISTINCT partner, document, substring( doc_date, 1, 6 ) AS buy_month
        WHERE partner IN @lt_r_partner
        INTO TABLE @DATA(lt_invoice).

      SELECT FROM @lt_invoice AS invoice
        FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
        GROUP BY buy_month
        ORDER BY doc_number DESCENDING, buy_month DESCENDING
        INTO TABLE @DATA(lt_result).
    ENDDO.
  ENDMETHOD.


  METHOD select_from_cds.
    DO c_times TIMES.
      SELECT FROM ZBS_I_DmoCTESumPerMonth
        FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
        GROUP BY buy_month
        ORDER BY doc_number DESCENDING, buy_month DESCENDING
        INTO TABLE @DATA(lt_result).
    ENDDO.
  ENDMETHOD.
ENDCLASS.

 

Conclusion

CTE or Common Table Expression is another way of doing complex operations from ABAP on the database. The temporary queries save the creation of Core Data Services or other views and break down complex statements in the program for better readability.

 

Source:
SAP Documentation - WITH