There was a requirement to get date name from a time stamp, which was stored as decimals in the tables.I need to use the ‘dayname’ date function of the sql query to convert to decimals to a string.
Asyou cannot use direct sql functions in CDS views,the only option was left to use CDS table function. As defined CDS table function is associated with an AMDP function, in which it is implemented using SQLScript Like any CDS entity. In the following example, I have no input parameters and it is not client dependent.
First, I created a table function using the statement ‘DEFINE TABLE FUNCTION’ in the ABAP CDS DDL.
@ClientDependent: false
@EndUserText.label: 'DEMO_TABLE_FUNCTION'
define table function DEMO_TABLE_FUNCTION
returns {
key guid:GUID;
weekdayname:string10;
st_time:string10;
}
implemented by method
Z_CL_AMDP_CDS_TBLFN=>FIND_DAY_FROM_DATES;
Here the output parameters are guid,weekdayname and st_time which are associated with corresponding data types.Now I will create an AMDP class and flag it as a CDS table function. Please note an AMDP function implementation for a CDS table function can only be declared in the public visibility section of a static AMDP class.
CLASS Z_CL_AMDP_CDS_TBLFN DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb .
CLASS-METHODS find_day_from_dates FOR TABLE FUNCTION DEMO_ICM_TABLE_FUNCTIO.
PROTECTED SECTION.
PRIVATE SECTION.
- ENDCLASS.
CLASS Z_CL_AMDP_CDS_TBLFN IMPLEMENTATION.
METHOD find_day_from_dates
BY DATABASE function FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY using table_name.
RETURN SELECT CASE_GUID as guid,DAYNAME ( TO_DATE ( SUBSTRING (create_time,1,8))) as weekdayname,TO_DATE ( SUBSTRING (create_time,1,8)) as st_time FROM table_name ;
- ENDMETHOD.
- ENDCLASS.
Create a class and declare interface ‘if_amdp_marker_hdb’ for HANA database(for other database use interface name as IF_AMDP_MARKER_(DB name)).Then declare the class method and use ‘FOR TABLE FUNCTION’ and the CDS table function name to declare the AMDP method as a CDS table function implementation . Not need to declare any importing exporting parameters in the declaration of method.
In the implementation of the class, declare method as an AMDP function. Use ‘RETURN’ statement to return to exporting parameters .The input and the output parameters in the AMDP function implementation are determined by the input parameters of the CDS table function.
Now create a CDS view in ABAP CDS DDL and use the CDS table function as a data source as the following.
@AbapCatalog.sqlViewName: 'CCMC'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Day based graph'
define view ZCOMBI_VIEWAND_FNTABLE as select from CDS_VIEW_NAME as VIEW_NAME1 inner join CDS_table_function_name as day_name on VIEW_NAME1. guid=day_name.guid {
key day_name.weekdayname as DayName,
VIEW_NAME1.description as Description,
count( * ) as DayNameCount
}
group by day_name.weekdayname, VIEW_NAME1.description
Note that I have not used input parameters in the CDS table function. If you are required to use CDS table function with parameters, please include then in the declaration of CDS table function as “define table function DEMO_TABLE_FUNCTION FnName with parameters() returns()implemented by method MethodName”.
