How To: Leverage Native HANA SQL-SCRIPT within ABAP CDS VIEWS

Introduction

ABAP CDS Views are SQL extensions, based on anyDB. There are several built-in functions (numeric, string, date/time, conversion) but, analogous to OpenSQL, that repertoire is limited and may not suffice for complex business requirements. In such situations, we may want to leverage the full HANA native SQLScript feature set:

  • Much more available functions and statements e.g. regular expressions, fuzzy search, window functions, factory calender, hierarchies,…
  • Specialized libraries e.g. text analysis, geospacial processing, machine learning
  • Declarative logic with loops and conditions, variable handling, procedure calls, full schema access

Exactly such integration of HANA native features into CDS Views is possible through AMDP (ABAP Managed Database Procedures). Those have been around in the ABAP world for quite a while and now it’s also available in CDS context.

How does it work?

Technically a CDS Table Function is created, which allows several importing- and exactly one returning parameter in form of a table. The Table Function is based on an AMDP Method, which provides the table content via native HANA SQLScript. Finally, the Table Function will be encapsulated within a CDS Hull View.

Implementation Example

As a simple example we are going to read Countries and Currencies from the SAP content view I_Country. We will follow this approach:

  • First create the CDS Table Function Z_TableFunctionView.
  • Then create the Class zcl_table_function with Method get_country.
  • Finally create the CDS Hull View Z_HullView to access the data.

1) CDS Table Function

Create a new CDS definition:

Use the table function template:

The definition involves three main parts:

The client handling (1) is done via systemField annotation. It implicitly assigns the ABAP system field sy-mandt to the parameter P_Client, which can then be passed to the AMDP method.

The returns section (2) defines the expected output fields and their ABAP data types.

The main logic will be implemented within method get_country (3) of class zcl_table_function.

2) AMDP Class Method

The definition involves once again three main parts:

The signature (1) comes from the table function.

The Method is defined (2) as function and the language is set to SQLScript with read-only ability (write operations are not supported). We want to read from View I_Country, so we need to make it known via USING keyword.

The return section (3) is where the “magic” happens, because there we have native SQLScript with the full power of HANA processing. Here we select three fields and filter on input parameter P_Client, which was defined earlier. We then use the function LIKE_REGEXPR to allow only upper-case letters from A to Z (at this point any regular expression could be used). We also use the CONTAINS function to leverage fuzzy search, meaning that the result shall show any currency that loosely resembles the string EUR. Such native HANA functions are otherwise not available within a regular ABAP CDS View.

3) CDS Hull View

The sole purpose of the Hull View is to encapsulate the Table Function. This is not required per se but is strongly recommended, because the Table Function itself does not support associations. That’s an essential CDS feature, which you most likely do not want to lose. Therefore, it is best practice to create a Hull View and place all further annotations and associations in here. In this example we use an association to additionally fetch the texts for each country.

The output works as expected. Due to the fuzzy search, also the currencies “ERN” and “MUR” are displayed:

Summary and considerations

AMDP acts as bridge between ABAP and HANA and enables powerful native SQLScript capabilities within CDS Views. Developers require knowledge in both technology worlds and there are some points to consider:

There is no automatic client handling within the Table Function, so this must be taken care of as shown in the example.

It’s important to be aware that ABAP and HANA operate on different data types. This may lead to unexpected results e.g. calculation deviations or different rounding.

Another aspect is the performance: While SQLScript offers the freedom to use imperative logic or nested procedure calls, that may break the execution optimizer and lead to bad runtimes. Also, the filter push downs must be considered by passing parameters to the table function.