Calling Built-in Functions in CDS Views

Objectives

After completing this lesson, you will be able to:
  • Use built-in functions in ABAP CDS
  • Use session variables

Built-in Functions in CDS - Overview

Built-in Functions in ABAP SQL

Now that you learned that you can use expressions in ABAP SQL and ABAP CDS in exactly the same way, what about the built-in functions? Can you use them in ABAP CDS, too?

Let's have a closer look.

Numeric Functions

For all numeric functions the answer is "yes". They are all supported in ABAP CDS as well.

String Processing

Most of the string processing functions are also supported. There are a few which are only available in ABAP SQL, mostly related to regular expressions. A detailed list can be found in the ABAP keyword documentation.

Date and Time Processing

The most restrictions exist in the area of data and time processing functions. Here, the generic functions (IS_VALID( ), ADD_DAYS( ), DAYS_BETWEEN( ), and so on) are only available in ABAP SQL. In ABAP CDS, you have to use the type-specific variants, that is, the corresponding functions starting with DATS, DATN, TIMS, TSTMP, and so on.

In addition, the functions to extract information from a date or time field are not supported in ABAP CDS, that means, you cannot use functions WEEKDAY( ), EXTRACT_MONTH( ), and so on in ABAP CDS. See the ABAP keyword documentation for a complete list.

Let's have a look at some examples.

Conversion Functions in ABAP CDS

Example: Currency Conversion in ABAP SQL and ABAP CDS

Play the video to see an example of currency conversion in ABAP SQL and ABAP CDS.

Some conversion functions have named parameters in ABAP SQL, but no parameter names in ABAP CDS. As a consequence, parameters which are optional in ABAP SQL become mandatory in ABAP CDS.

Let us take the timestamp conversion function TSTMP_TO_DATS() as an example. The function has four parameters, of which two are mandatory and two are optional in ABAP SQL. In ABAP CDS, all four parameters are mandatory and they are passed without explicit parameter assignment.

Session Variables

Inside an ABAP CDS view entity, you can use session variables to access the value of ABAP system fields. Access to a session variable starts with prefix $session., followed by the variable name.

The example uses session variable system_language in a filter expression and session variable system_date in the WHERE clause. But you can use session variables I almost any other operand position, too.

Caution

Session variables only contain values if the reading request comes from an ABAP system. In any other situation, the values of session variables are undefined! That is why you should prefer input parameters to access ABAP system fields. See the next lesson for details.

Currently, ABAP CDS offers six built-in session variables. The list shows them together with the related system field.

Note

In addition to the built-in session variables, there are also application session variables. They are required for specific applications and they are set by dedicated system programs. For details, refer to the ABAP keyword documentation.

Use Built-in Functions in a CDS View

You want to extend your CDS view entity with SQL logic. You use SQL functions to concatenate first name and last name of the department assistant, convert the monthly salary to a unique currency and calculate the company affiliation, that is, the number of days an employee works for the company.

Template:

  • /LRN/C_EMPLOYEE_EXP (Data Definition)

Solution:

  • /LRN/C_EMPLOYEE_FCT (Data Definition)

Prerequisites

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

Task 1: Use a String Function

In you CDS view entity for evaluating employee data (suggested name was: Z##_C_EmployeeQuery, where ## is your group number), adjust the definition of the AssistantName view element. Use a suitable string function to concatenate the first name and the last name of the department assistant, separated by a single space.

Steps

  1. Edit the element list of your view entity. Comment the view element that derives the last name of the department assistant.

    1. Place the cursor anywhere in the respective code row and press Ctrl + < to add a comment sign (//) at the beginning of the row.

  2. After the old view element, use code completion to add a call of SQL function CONCATENATE_WITH_SPACE as a new view element.

    1. In the code row // _Department._Assistant.LastName as AssistantName,, place the cursor after the colon and press Enter to insert a new row.

    2. Enter con and press Ctrl + Space to invoke code-completion.

    3. From the suggestion list, choose concat_with_space(arg1, arg,2, space_cnt) (function) and press Shift + Enter to insert the full signature.

      Result

      This should insert the following code:

      Code Snippet
      123
      
  concat_with_space( arg1, arg2, space_cnt )

  3. Replace the placeholders with suitable view elements and literals.

    1. Adjust the code as follows:

      Code Snippet
      12345
      
concat_with_space( _Department._Assistant.FirstName, 
                   _Department._Assistant.LastName, 
                   1 )

      Note

      We inserted additional line breaks to increase readability in the printed course material.

  4. Provide the same view element name that was used for the removed view element (suggested name was: AssistantName).

    1. Adjust the code as follows:

      Code Snippet
      12345
      
concat_with_space( _Department._Assistant.FirstName, 
                   _Department._Assistant.LastName, 
                   1 ) as AssistantName,

  5. Activate and test the CDS view entity.

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

    2. Press F8 to test the CDS view entity in the Data Preview tool.

Task 2: Use a Date Function

In a new view element (suggested name: CompanyAffiliation), use a suitable date function to calculate the employee's company affiliation in days, that is, the number of days between the entry date and today. Then divide the result by the number of days per year to calculate the employee's company affiliation in years, with a precision of 1 decimal place.

Hint

The current date is available in session variables $session.user_date and $session.system_date. For this exercise, use the current system date.

For simplicity, disregard the existence of leap years and assume a year has 365 days.

Steps

  1. Edit the element list of your view entity. Before the exposed association, use code completion to add a call of SQL function DATS_DAYS_BETWEEN as a new view element.

    1. Place the cursor at the beginning of code row /* Associations */ and press Enter to insert a new row.

    2. In the new code row, enter dats and press Ctrl + Space to invoke code-completion.

    3. From the suggestion list, choose dats_days_between( date1, date2 ) (function) and press Shift + Enter to insert the full signature.

      Result

      This should insert the following code:

      Code Snippet
      123
      
  dats_days_between( date1, date2 )

  2. Replace the placeholders with suitable view elements and session variables and specify a view element name (suggested name: CompanyAffiliation).

    1. Adjust the code as follows:

      Code Snippet
      1234
      
  dats_days_between( EntryDate, 
                     $session.system_date ) as CompanyAffiliation,

      Note

      Again, we inserted additional line breaks to increase readability in printed course material.

  3. Use the SQL function division to calculate the company affiliation in years, with a precision of one decimal place.

    1. Adjust the code as follows:

      Code Snippet
      123456
      
  division( dats_days_between( EntryDate, 
             $session.system_date ), 
             365, 
             1 ) as CompanyAffiliation,

  4. Activate and test the CDS view entity.

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

    2. Press F8 to test the CDS view entity in the Data Preview tool.

Task 3: Perform a Currency Conversion

In a new view element (suggested name: AnnualSalaryConverted), use a suitable SQL function to convert the content of view element AnnualSalary to currency US Dollar. Use the current system date as exchange rate date. Then adjust the calculation of view element MonthlySalary. Use the converted annual salary as input instead of the unconverted annual salary.

Hint

You can re-use calculated view elements of the same view entity by using the $projection prefix.

Steps

  1. Remove or comment view element CurrencyCode.

    1. Place the cursor in the code row CurrencyCode, and press Ctrl + < to add a comment sign (//) at the begin of the row.

  2. In the next code row, define a new view element (suggested name: CurrencyCodeUSD) in which you convert literal 'USD' to predefined type abap.cuky.

    1. Adjust the code as follows:

      Code Snippet
      1234
      
// CurrencyCode,
  cast( 'USD' as /dmo/currency_code ) as CurrencyCodeUSD,

  3. Before the MonthlySalary view element, use code completion to insert a call of the SQL function currency_conversion.

    1. In a new code row, enter curr and press Ctrl + Space to invoke code-completion.

    2. From the suggestion list, choose the entry starting with currency_conversion( amount => amount, ... and press Shift + Enter to insert the full signature.

    3. The code should now look as follows:

      Code Snippet
      123456789101112
      
  currency_conversion( amount             => amount,
                       source_currency    => source_currency ,
                       target_currency    => target_currency,
                       exchange_rate_date => exchange_rate_date 
                     )

   @EndUserText.label: 'Monthly Salary'
   @Semantics.amount.currencyCode: 'CurrencyCode'
    cast( AnnualSalary as abap.fltp ) / 12.0 as MonthlySalary,

      Note

      We inserted additional line breaks, again.

  4. Replace the placeholders with values for the parameters. As input, use the annual salary and the currency code from the source view, own view element CurrencyCodeUSD, and system variable system_date .

    1. Adjust the code as follows:

      Code Snippet
      1234567
      
  currency_conversion( amount             => AnnualSalary,
                       source_currency    => CurrencyCode ,
                       target_currency    => $projection.CurrencyCodeUSD,
                       exchange_rate_date => $session.system_date 
                     )

  5. Add an element name (suggested name: AnnualSalaryConverted), a label, and the mandatory Semantics.amount.CurrencyCode annotation.

    1. Adjust the code as follows:

      Code Snippet
      123456789
      
  @EndUserText.label: 'Annual Salary'
  @Semantics.amount.currencyCode: 'CurrencyCodeUSD'
  currency_conversion( amount             => AnnualSalary,
                       source_currency    => CurrencyCode ,
                       target_currency    => $projection.CurrencyCodeUSD,
                       exchange_rate_date => $session.system_date 
                     ) as AnnualSalaryConverted,

  6. Adjust the calculation of view element MonthlySalary. Instead of AnnualSalary, use the new view element AnnualSalaryConverted as input.

    1. Adjust the code as follows:

      Code Snippet
      123456
      
  @EndUserText.label: 'Monthly Salary'
  @Semantics.amount.currencyCode: 'CurrencyCode'
  cast( $projection.AnnualSalaryConverted as abap.fltp ) 
  / 12.0 as MonthlySalary,

  7. Adjust the view element name (suggested name: MonthlySalaryConverted) and adjust the mandatory Semantics.amount.CurrencyCode annotation.

    1. Adjust the code as follows:

      Code Snippet
      123456
      
  @EndUserText.label: 'Monthly Salary'
  @Semantics.amount.currencyCode: 'CurrencyCodeUSD'
  cast( $projection.AnnualSalaryConverted as abap.fltp ) 
  / 12.0 as MonthlySalaryConverted,

  8. Activate and test the CDS view entity.

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

    2. Press F8 to test the CDS view entity in the Data Preview tool.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn