This is a test message to test the length of the message box.
Login
ABAP CDS Functions
Created by Software-Heroes

CDS - Functions

3043

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.

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.


Included topics:
CDSCore Data ServiceFunctions
Comments (0)



And further ...

Are you satisfied with the content of the article? We post new content in the ABAP area every Friday and irregularly in all other areas. Take a look at our tools and apps, we provide them free of charge.


ABAP Tools - Work with Eclipse (CDS Analysis)

Category - ABAP

In the complex world of CDS views, it is important to have the right tool at hand to ensure an analysis of the structures and data sources.

08/25/2023

CDS - View Entity

Category - ABAP

In this article you will learn more about the new Core Data Service entities, what they bring and what makes them different from the old views.

07/29/2022

CDS - Learnings

Category - ABAP

In this article we summarize the learned content and show you the way for what you will need the CDS Views in the future.

06/10/2022

CDS - Virtual fields

Category - ABAP

This article is about virtual fields in Core Data Services and how you can subsequently deliver such complex data.

06/03/2022

CDS - Authority check

Category - ABAP

How and where are the permissions for a Core Data Service delimited? Check out this article for the details.

05/20/2022