###### Technical Articles

# 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

## Definition

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:

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:

```
CLASS-METHODS execute
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:

```
METHOD execute BY DATABASE FUNCTION
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.
result = portion / total * 100;
ENDMETHOD.
```

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,
DEMO_CDS_SCALAR_RATIO(
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
case
when p2: reference type of p1
then #NONE
else reference type of p1
end;
```

**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.

**Example**

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
ratio_of(
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

- ABAP Keyword Documentation, Analytical Scalar Functions
- ABAP Keyword Documentation, SQL-Based Scalar Functions
- ABAP Data Models Guide, Scalar Functions
- Video: ABAP Cloud: CDS Scalar Functions

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

Hi,

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

Fabian

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.

Regards,

R

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.

Best

Andrea

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.

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

Good luck.