A CDS Scalar function ( This feature is supported as of SAP S/4HANA 2023 FPS 00 ) allows us to define a custom function directly on SAP HANA database using SQLSCRIPT and then reuse it in CDS view entities.
There are 3 parts in CDS scalar function –
A CDS scalar function definition — this defines the Definition of Scalar function i.e, in and out parameters. There will be one or many input parameters and exactly one output parameter.
Let’s make this a CDS scalar function which will accept a date and returns respective weekday value( Mon, Tue, Wed, Thu, Fri, Sat ).
2. Define the CDS scalar function implementation reference. This refer to an AMDP class and method having the logic. This part won’t have actual implementation.
It follows the scalar function name followed by _SQL
Click on next and provide an AMDP class and its method which will hold the implementation logic.
3) Next, lets create our AMDP class (ZCL_AMDP_SF) and a static method (get_weekday).
Here below is the code for AMDP class and method.
CLASS zcl_amdp_sf DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
CLASS-METHODS get_weekday FOR SCALAR FUNCTION zcds_scalar_func.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_amdp_sf IMPLEMENTATION.
METHOD get_weekday BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
declare weekday_num int;
weekday_num := weekday( input_Date );
IF :weekday_num = 0 THEN result = ‘MON’; END if;
IF :weekday_num = 1 THEN result = ‘TUE’; END if;
IF :weekday_num = 2 THEN result = ‘WED’; END if;
IF :weekday_num = 3 THEN result = ‘THU’; END if;
IF :weekday_num = 4 THEN result = ‘FRI’; END if;
IF :weekday_num = 5 THEN result = ‘SAT’; END if;
IF :weekday_num = 6 THEN result = ‘SUN’; END if;
ENDMETHOD.
ENDCLASS.
In the implementation , we have added logic to find the number as per input date and using if else logic , we are providing 3 char weekday name for same.
To test this, we have to create another CDS view which will call this CDS scalar function.
Let’s execute the CDS view to get output of Scalar function.
A CDS Scalar function ( This feature is supported as of SAP S/4HANA 2023 FPS 00 ) allows us to define a custom function directly on SAP HANA database using SQLSCRIPT and then reuse it in CDS view entities.There are 3 parts in CDS scalar function -A CDS scalar function definition — this defines the Definition of Scalar function i.e, in and out parameters. There will be one or many input parameters and exactly one output parameter.Let’s make this a CDS scalar function which will accept a date and returns respective weekday value( Mon, Tue, Wed, Thu, Fri, Sat ).2. Define the CDS scalar function implementation reference. This refer to an AMDP class and method having the logic. This part won’t have actual implementation.It follows the scalar function name followed by _SQLClick on next and provide an AMDP class and its method which will hold the implementation logic.3) Next, lets create our AMDP class (ZCL_AMDP_SF) and a static method (get_weekday).Here below is the code for AMDP class and method.CLASS zcl_amdp_sf DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
CLASS-METHODS get_weekday FOR SCALAR FUNCTION zcds_scalar_func.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_amdp_sf IMPLEMENTATION.
METHOD get_weekday BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
declare weekday_num int;
weekday_num := weekday( input_Date );
IF :weekday_num = 0 THEN result = ‘MON’; END if;
IF :weekday_num = 1 THEN result = ‘TUE’; END if;
IF :weekday_num = 2 THEN result = ‘WED’; END if;
IF :weekday_num = 3 THEN result = ‘THU’; END if;
IF :weekday_num = 4 THEN result = ‘FRI’; END if;
IF :weekday_num = 5 THEN result = ‘SAT’; END if;
IF :weekday_num = 6 THEN result = ‘SUN’; END if;
ENDMETHOD.
ENDCLASS. In the implementation , we have added logic to find the number as per input date and using if else logic , we are providing 3 char weekday name for same. To test this, we have to create another CDS view which will call this CDS scalar function.Let’s execute the CDS view to get output of Scalar function. Read More Technology Blog Posts by Members articles
#SAP
#SAPTechnologyblog
