Implementing SQL Expressions in CDS Views

Objectives

After completing this lesson, you will be able to:

  • Motivate the use of code pushdown in CDS Views
  • Use SQL expressions in CDS views

Code Pushdown in ABAP SQL and ABAP CDS

In a previous part of this learning journey, you learned that with SAP HANA's enormous data analysis capabilities, you are recommended to do calculations already on the database whenever this is possible. You also learned that ABAP SQL offers plenty of expressions and built-functions for this purpose.

Most of these expressions and built-in functions are also available in CDS view definitions. This allows you to move the complicated calculations into a CDS view definition and then retrieve the data from the CDS view.

Let us look at an example. In this ABAP code, the ABAP SQL statement uses an arithmetic expression to calculate the number of free seats on a flight by subtracting the number of occupied seats from the total number of seats.

In this next example, the arithmetic expression is moved into a CDS view definition. The calculation result is visible to consumers of the view as view element seats_free. The ABAP code reads view element seats_free like any other view element.

Note

For the consumer of a CDS view, it makes no difference, if a view element is simply a projection of a database table field or the result of a complicated calculation.

Moving the SQL logic from ABAP SQL into a CDS view has several advantages:

Encapsulation

Because the SQL logic is hidden in the CDS view definition, the ABAP SQL statements become easier to read.

Reuse

By reading from the same CDS view, you can easily reuse the SQL logic in other applications. In particular, you can use the SQL logic in the frameworks that use CDS views, for example, embedded analytics, CDS-based data extractors for SAP BW, ABAP RESTful application programming and many more.

Semantics

You can add semantics to the calculation result by giving the element a meaningful name and by annotating it with element annotations.

Authorization Rules

You can restrict the access to the calculated data by creating a CDS access control for the CDS view.

Literals in ABAP CDS

When you compare literals in ABAP SQL and literals in ABAP CDS you will notice that ABAP CDS offers a larger variety in types.

Character Literals

While ABAP SQL only knows character literals of predefined type C (corresponding to dictionary type CHAR) , ABAP CDS uses predefined dictionary type NUMC, when the character literal consists of only digits.

Numeric Literals

ABAP SQL only knows positive and negative integer literals and uses predefined ABAP type I (dictionary type INT4) for all of them.

ABAP CDS on the other hand, also uses dictionary types INT4, INT2, and INT1, choosing the shortest type that can hold the value of the literal. Literal value 255, for example is still of type INT1, while 256 is too large for one byte and requires type INT2.

ABAP CDS also knows non-integer literals. They contain a decimal point and have predefined dictionary type FLTP.

Note

There is no support for non-integer literals in ABAP SQL –- mostly because the period sign (.) is reserved as statement delimiter.

Type Conversions

Like ABAP SQL, you can use the CAST expression to implement technical type conversions.

The operand for the cast expression cannot only be a literal, like in our example. All kinds of other expressions are possible, for example, fields of the data source, arithmetic expressions, CASE distinctions, SQL functions, and so on.

The target type can be specified as a predefined dictionary type. This is identical to the use of CAST in ABAP SQL. However, note that in CDS, a prefix abap. is required.

There are restrictions with regard to the combination of source type and target type. Some combinations are not supported at all. Others only work with limitations. For example, it is currently not possible to use operands of type FLTP as source for the CAST expression. A detailed matrix can be found in the ABAP language documentation.

It is a specialty of CAST in ABAP CDS that you can also use a dictionary data element as target type. This kind of cast not only converts the technical type. It also takes semantic information like the field labels from the data element.

A cast with a data element as target type can even be useful, if the technical type of the operand and the target data element are identical. In this case, only the semantic information of the operand is modified. If a cast does not change the technical type, the syntax check issues a warning. You can suppress this warning with addition PRESERVING TYPE inside the cast expression.

CASE Distinctions in ABAP CDS

ABAP CDS offers exactly the same CASE distinctions as ABAP SQL. Simple case and complex case distinctions follow the same syntax rules and have the same functionality.

This can be illustrated with an example. The simple case expression on the left comes from an ABAP SQL SELECT statement. The simple case expression on the right is part of a CDS view entity definition. There is no difference - apart from the different formatting and coloring in the two editors.

Arithmetic Expressions in ABAP CDS

Like ABAP SQL, arithmetic expressions can be used in the element list of a CDS View definition. The same rules and restrictions apply in both cases. As is the case in ABAP SQL, the most important restriction is that the division operator (/) is only allowed in floating point expressions.

Hint

You can use any numeric operand in a floating point expression by converting the type to FLTP using CAST( ).

Compare these examples from ABAP SQL and ABAP CDS. Again, the difference is mostly formatting. There are two differences which are not really related to the arithmetic expressions but rather to the use of literals and cast expressions:

  • The cast expression in ABAP CDS requires prefix "abap." in front of predefined dictionary types.
  • You can use floating point literals in ABAP CDS. You can use literal 100.0 directly in the floating point expression. It is not necessary to use integer literal 100 and cast it to predefined type FLTP.

Log in to track your progress & complete quizzes