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 statementDEFINE TABLE FUNCTIONin 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.

  1. 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  ;

  1. ENDMETHOD.
  2. 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”.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply