CDS - Functions

Table of contents

• Code-Pushdown
• CASE
• CAST
• Session
• SUBSTRING
• Additional functions
• Conclusion

Article update: Since release 7.57 (S/4 HANA 2022), DEFINE VIEW is marked as obsolete, you should use DEFINE VIEW ENTITY instead. These may differ from the examples in some places. You can find more information about the new views in this article.

---

In the last article we built the data model together with you and filled it with data. Today we are building the first CDS views together and showing you the benefits of functions. In doing so, we'll cover a few key features, but we won't go into all of them in detail.

 

Code-Pushdown

Core Data Services can do more than combine data in a view and make it available under a new name. The great added value comes from the numerous functions that ABAP provides to change or compare the data directly in the database. The use of the functions directly on the database is the so-called code pushdown, since we give "functions" to the database and only expect the result via the database interface.

 

CASE

In this example we want to use a case to identify all erroneous positions in our data sets. We don't want to trust the user to manually search for an incorrect number, we want to provide a simple true/false to recognize the entries.

@AbapCatalog.sqlViewName: 'ZBSCDMOPOSERR'
@EndUserText.label: 'Positions with error'
define view ZBS_C_DmoPositionError
  as select from ZBS_I_DmoPosition
{
  key DocumentNumber,
  key PositionNumber,
      MaterialNumber,
      PositionQuantity,
      PositionPrice,
      PositionCurrency,
      case PositionPrice
        when 37707 then 'X'
        else ' '
      end as ErrorInConversion
}

 

The case checks the content of a database field, this can be queried with different when-then combinations and a corresponding value can be set. After Else you can then process the rest of the untreated field contents. The control structure ends with End and a new field is defined at the end. The field can then be addressed under this field name. Let's take a look at the data preview in Eclipse:

 

Das Feld "ErrorInConversion" wird entsprechend gesetzt und kann angesprochen werden, um so die Daten zu filtern. Beachte aber, dass du das neu erstellte Feld nicht für andere Funktionen oder in der Where-Kondition im View verwenden kannst.

 

CAST

An equally important function is the CAST, with which you can change data types in the CDS View without changing the actual data type on the database. This allows, for example, great interface formats to be created without having to set up a structure specifically for this. The following example for this:

@AbapCatalog.sqlViewName: 'ZBSCDMODISCAST'
@EndUserText.label: 'Cast from number'
define view ZBS_C_DmoDicountCast
  as select from ZBS_I_DmoDiscount
{
  key PartnerNumber,
  key MaterialNumber,
      DiscountValue,
      concat( cast( DiscountValue as abap.char(15) ), ' %' ) as DiscountText
}

 

Here we connect the cast function with a concat to append a " %" after the character-based conversion. The cast function tries to do the conversion, see the official documentation for more details. The result of the conversion now looks like this.

 

Session

Sometimes it makes sense to check for the current user or the current date and only give certain records to the caller. Such dependent information is made available in the session. You can use such session variables as fields, in on-conditions or where conditions. The following variables are available:

 

If the session variable is to be made available as a field, then it also needs a corresponding alias so that it is integrated in the view as a field. This is exactly how you can use the session to limit the data using the where condition. As an example the following view:

@AbapCatalog.sqlViewName: 'ZBSCDMOINVSESS'
@EndUserText.label: 'Session information'
define view ZBS_C_DmoInvoiceSession
  as select from ZBS_I_DmoInvoice
{
  key DocumentNumber,
      DocumentDate,
      $session.system_language as SystemLanguage
}
where
  DocumentDate < $session.system_date

 

If we look at the data preview, we see the filled "SystemLanguage" field with the current logon language.

 

SUBSTRING

Here is another function for processing character strings or, as in our example, for a date. We want to extract the month of the document date and make it available as a separate column. We can use the substring function, which you should also know from ABAP.

@AbapCatalog.sqlViewName: 'ZBSCDMOINVSUB'
@EndUserText.label: 'Substring for month'
define view ZBS_C_DmoInvoiceSubstring
  as select from ZBS_I_DmoInvoice
{
  key DocumentNumber,
      DocumentDate,
      substring( DocumentDate, 5, 2 ) as MonthInDocumentDate,
      PartnerNumber
}

 

The result now looks like this. However, you should note that the result of the substring is a character string and therefore the field has a corresponding data type. If you then want to convert the month into a number, you can connect the whole thing with a cast.

 

Additional functions

In this article we just wanted to give you a few examples of functions, there are many more that you can use. Here are a few more examples:

• upper/lower - Conversion of texts to upper/lower
• concat_with_space - Concatenate with spaces as separators
• coalesce - If the first value is null, then the second value is returned
• dats_add_days - Add days to a date
• dats_days_between - Days between two dates

 

Conclusion

By using functions, the database has more to do and we use it to push down the code. The operations do not have to be carried out on the application server, you only receive the result of the query.