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
- ABAP Release 7.58
- SAP S/4HANA 2023
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:
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:
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.
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
- SAP Managed Tags:
7 Comments
