Skip to Content
Technical Articles
Author's profile photo Andrea Schlotthauer

ABAP CDS Release News 2308 – CDS Scalar Functions Implemented by AMDP

The predefined CDS functions do not cover the scope of your business scenario? With the introduction of SQL-based CDS scalar functions, partners and customers are empowered to define their own CDS scalar functions with a SQL implementation via AMDP and to use them in CDS view entities. Complex calculations may now be outsourced from single CDS entities and still be pushed down to the HANA engine.

Release Info

  • SAP BTP, ABAP Environment 2308


An SQL-based scalar function is a user-defined function that accepts multiple input parameters and returns exactly one scalar value. A scalar function allows developers to encapsulate complex algorithms into manageable, reusable code that can then be used in all operand positions of CDS view entities that expect scalar values. A scalar function is linked with an AMDP function in which it is implemented using SQLScript.

SQL-based scalar functions make AMDP scalar functions defined in AMDP known to ABAP Dictionary and available in ABAP CDS.

The following figure shows the design time of a CDS scalar function:


Design time of an SQL-based scalar function

A SQL-based scalar function is defined in a scalar function definition using the keyword DEFINE SCALAR FUNCTION. The scalar function implementation reference binds the function to a runtime and to an existing AMDP function. When used in the SELECT list of a CDS view entity, the CDS framework executes the scalar function by calling the associated function on the database.

Creating a CDS SQL-based scalar function

In order to create a CDS SQL-based scalar function, you need the following three objects:

  • A CDS scalar function definition defined using DEFINE SCALAR FUNCTION.
  • A CDS scalar function implementation reference that binds the scalar function to a runtime engine and to an AMDP function implementation.
  • An AMDP method that implements the CDS scalar function as database function in SQLScript.

Here’s an example:

CDS scalar function definition:

define scalar function DEMO_CDS_SCALAR_RATIO
  with parameters
    portion: numeric
    total  : type of portion
  returns abap.decfloat34

A CDS scalar function has input parameters defined after WITH PARAMETERS and it returns a scalar result with the data type defined after RETURNS.

Scalar function implementation reference

The scalar function implementation reference is defined in a form-based tool in the ABAP Development Tools. It binds a CDS scalar function definition to a runtime and to an AMDP method that implements the function.

AMDP function implementation

After activating the CDS scalar function, you can go on implement the functional AMDP method in an AMDP class, that is a class with the marker interface IF_AMDP_MARKER_HDB. An AMDP method for a CDS scalar function must be a static functional method of a static AMDP class that is declared as follows:

              FOR SCALAR FUNCTION demo_scalar_function.

The declaration is linked directly to the CDS scalar function. The parameter interface is implicitly derived from the scalar function’s definition! Implementation looks like you might expect it:

                 FOR HDB
                 LANGUAGE SQLSCRIPT
                 OPTIONS READ-ONLY.
    result = portion / total * 100;

The implementation is done in native SQLScript for a HANA database function.

Note that client handling has not yet been implemented and therefore, only client-independent objects can be used in the implementation. Client handling will be implemented with a later release.

Use in a CDS view entity

A SQL-based scalar function can be used in CDS view entities in operand positions that expect scalar values, similar to built-in functions. Here’s an example for a scalar function used in the SELECT list of a CDS view entity:

define view entity DEMO_CDS_SCALAR_USE_RATIO
  as select from sflight
  key carrid                  as Carrid,
  key connid                  as Connid,
  key fldate                  as Fldate,
      seatsocc                as BookedSeats,
      seatsmax                as TotalSeats,
        portion => seatsocc, 
        total   => seatsmax ) as OccupationRatio

Reference handling

SQL-based scalar functions support the handling of CDS amount fields and CDS quantity fields. CDS amount fields and CDS quantity fields are fields with a reference to a currency key, a unit key, or a calculated unit. Scalar functions can handle these references. You can define which reference types are allowed for each input parameter and for the return parameter. If the actual parameters passed to the input parameters use reference types that are not explicitly allowed, a syntax check error occurs.

The following reference types are available:

  • #CUKY
  • #UNIT
  • #CALC
  • #NONE

A parameter can also be typed with reference to another parameter. This means that it inherits the reference type of the referenced parameter. The syntax is WITH REFERENCE TYPE OF.

The reference type can also be defined dynamically, depending on the reference types of the input parameters. This is done using CASE statements.

Here’s an example

define scalar function DEMO_CDS_SCALAR_REF_CASE
  with parameters
    p1: numeric
      with reference type [ #CUKY, #UNIT, #CALC, #NONE ],
    p2: numeric
      with reference type [ #CUKY, #UNIT, #CALC, #NONE ],
   p3: abap.dec(4,2)
      with reference type [ #CUKY, #UNIT, #CALC, #NONE ]
  returns abap.dec(4,2)
    with reference type
        when p2: reference type of p1
          then #NONE
        else reference type of p1

Analytical scalar functions

A CDS scalar function can also be bound to an analytical engine. In this case, it can be used in CDS analytical queries and it is evaluated by the ABAP Analytical Engine.

Analytical scalar functions are defined and implemented by SAP. They are provided to customers and partners as CDS system functions.

For a complete list of SAP-delivered analytical CDS scalar functions, see the ABAP Keyword Documentation, topic ABAP CDS – Analytical Scalar Functions.


The following example demonstrates how to use an analytical scalar function in an analytical projection view. The analytical scalar function RATIO_OF has two mandatory input parameters: portion and total. It calculates the ratio of portion in relation to total. The actual parameters are passed using an arrow =>. You see that CDS expressions and functions can be passed as actual parameters.

@EndUserText.label: 'Analytical scalar function'
@AccessControl.authorizationCheck: #NOT_ALLOWED

define transient view entity DEMO_CDS_USE_ANA_SCALAR
  provider contract analytical_query
  as projection on DEMO_CDS_CUBE_VIEW
@Aggregation.default: #FORMULA
  portion => ( get_numeric_value( amount_sum )
               - get_numeric_value( amount_sum )
               * $projection.Discount ) * $projection.Tax,
  total => get_numeric_value( amount_sum ) )
as AmountRatioFinalToOriginal

For more information see

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo MiHa Nguyen
      MiHa Nguyen

      Dear Andrea,

      thank you very much for a very informative blog. I wonder if the scalar funtion e.g. ratio_of is available for S4HANA On Premise e.g. 2023 ?

      I was trying it on my system and got the error : unsupported function parameter in conversion: portion[Analytics].

      Thank you and kind Regards, MH

      Author's profile photo Fabian Fellhauer
      Fabian Fellhauer


      in which version of S/4 HANA do you currently face this issue? And how does your CDS definition look like?

      In general this feature will also be supported in S/4HANA On Premise 2023.

      Thank you and best regards


      Author's profile photo Rishi R
      Rishi R

      interesting feature, thanks for the blog. My question could be basic, could you please let me know the difference between scalar function and a virtual field?

      is there a performance advantange? I see we use pure ABAP for virtual fields, here we are using SQL script.


      Author's profile photo Andrea Schlotthauer
      Andrea Schlotthauer
      Blog Post Author

      Hi R,

      The big advantage of CDS scalar functions compared to virtual fields is that they are executed on the database and you can stack further CDS/DB artifacts on them and then push the whole construct to the database. There is usually better performance when larger amounts of data are involved. Views with virtual fields can only be called from ABAP and you cannot stack further database artifacts on them.

      Plus, virtual fields are only evaluated by special frameworks such as the RAP query engine. When accessing a view with a virtual field using ABAP SQL, the ABAP class is not accessed. Scalar functions, on the other hand, will soon also be available for consumption in ABAP SQL.

      Hope this helps.



      Author's profile photo Mohammad Naushad Alam
      Mohammad Naushad Alam

      Hi Andrea,

      Indeed, it will elevate AMDP to the next level. Thank you for providing all the details. While I was trying to incorporate them into our business logic, I encountered an authorization error:

      It didn't trigger any logs in SU53. Therefore, could you please suggest what kind of role would be required? Thanks in advance.

      Author's profile photo Andrea Schlotthauer
      Andrea Schlotthauer
      Blog Post Author

      Hi, there is an authorization check on S_DEVELOP. You need activity 02 (modify).

      Good luck.