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.

Use Expressions in a CDS View

You want to extend your CDS view entity with SQL logic. In a first step, you tag employees as department heads and assistants and calculate the monthly salary.

Template:

  • /LRN/C_EMPLOYEE_QRY (Data Definition)

Solution:

  • /LRN/C_EMPLOYEE_EXP (Data Definition)

Prerequisites

For this exercise, you need the CDS view entity you created in a previous exercise (suggested name was: Z##_C_EmployeeQuery, where ## is your group number). If you have not finished that exercise, create a copy of the data definition /LRN/C_EMPLOYEE_QRY.

Task 1: Use CASE Distinction

In your CDS view entity for evaluating employee data (suggested name was: Z##_C_EmployeeQuery, where ## is your group number), define a new view element (suggested element name: EmployeeRole). Make the element return value 'H' if the employee is the department head, value ' A' if the employee is the department assistant, and ' ' for all other employees.

Steps

  1. In the element list of your view entity, before the exposed association, add a case distinction that compares the value of EmployeeID to the value of element HeadID in the target of association _Department. If the values are identical, return value 'H'.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789101112
      
define view entity Z##_C_EmployeeQuery
  as select from Z##_R_Employee
  {
    key EmployeeId,
        FirstName,
        LastName,
        DepartmentId,

        _Department.Description         as DepartmentDescription,
        _Department._Assistant.LastName as AssistantName,

  2. Add a second branch that compares the value of EmployeeID to the value of element AssistantID in the target of association _Department. If the values are identical, return value 'A'.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123
      
  case EmployeeId
  when _Department.HeadId      then 'H'

  3. Add an else branch to return value ' '.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      1234
      
  case EmployeeId
    when _Department.HeadId      then 'H'
    when _Department.AssistantId then 'A'

  4. Close the CASE expression and specify an element name (suggested element name: EmployeeRole).

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345
      
  case EmployeeId
    when _Department.HeadId      then 'H'
    when _Department.AssistantId then 'A'
    else ' '

  5. Add the required annotation to define a label for the calculated field (suggested value: Employee Role).

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
  case EmployeeId
    when _Department.HeadId      then 'H'
    when _Department.AssistantId then 'A'
    else ' '
  end as EmployeeRole,

Task 2: Use Arithmetic Expression

Based on the employee's annual salary, calculate the monthly salary (suggested element name: MonthlySalary).

Steps

  1. Use an arithmetic expression with the / operator to divide the content of AnnualSalary by 12.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
  @EndUserText.label: 'Employee Role'
  case EmployeeId
    when _Department.HeadId      then 'H'
    when _Department.AssistantId then 'A'
    else ' '
  end                             as EmployeeRole,

  2. Add the required annotation to define a label for the calculated field (suggested value: Monthly Salary).

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      1234567891011
      
  @EndUserText.label: 'Employee Role'
  case EmployeeId
    when _Department.HeadId      then 'H'
    when _Department.AssistantId then 'A'
    else ' '
  end                             as EmployeeRole,

 cast( AnnualSalary as abap.fltp ) / 12.0 as MonthlySalary,

  3. Add the CurrencyCode element to the element list and use the @Semantics.amount.currencyCode annotation to link it to the MonthlySalary element.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123
               
 @EndUserText.label: 'Monthly Salary'
 cast( AnnualSalary as abap.fltp ) / 12.0 as MonthlySalary,

  4. Activate the CDS data definition.

    1. Press Ctrl + F3 to activate the development object.

  5. Open the Data Preview and analyze the output.

    1. Press F8 to open the Data Preview.

Log in to track your progress & complete quizzes