CDS - Functions
In this article we deal with the functions in the CDS area and how you should primarily use them, but also where they help you.
Table of contents
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.