Table Function – SAP Cloud Platform

It is a type of CDS which allow developers to write database procedures They support the HANA platform code pushdown capabilities in ABAP CDS.

For creating a table function two components are required

◉ CDS entity of the table function
◉ CDS table function implementation

The main advantage of using the table function

Complex logic can be implemented easily with the help of SQL Script and SQL Script gives much more functionality and flexibility.

Creating Table Function:

Step 1: Go to Project Package.

Step 2: Go to Core Data Services -> Data Definitions  

Step 3: Right-click -> New Data definition

Step 4: Give a name and description

Step 5: Choose the Transport Request and Click Next

Step 6: Select Define Table Function with Parameters, then click Finish.

Step 7: Syntax for the CDS is as follows

@EndUserText.label: 'table_function_description'

define table function table_function_name

returns {

  client_element_name : abap.clnt;

  element_name : element_type;

}implemented by method class_name=>method_name;

Step 8: Go back to the Project Explorer Source Code Library -> Classes

Step 9: Right-click and create a New ABAP Class

So we have created the CDS entity and table function implementation class.

In the following example, I am creating a table function without any parameter, and return a total of two elements. From my experience, I haven’t found any limitation for the number of elements to be returned (I have tried returning ninety elements).

@EndUserText.label: 'Table function demo'

define table function Z_TABLE_FUNCTION_DEMO

returns {

  client : abap.clnt;

  empid : abap.numc(10);

  qualification : abap.char(50); 

}

implemented by method z_amdp_cl_tf=>get_data;

We have to specify the implementation class and method in the ‘implemented by method’ section and the element to be returned in the ‘return’ section.

Before activating the table function CDS we must implement the marker interface ‘if_amdp_marker_hdb’ in the implementation class and also define a method for the table function (CLASS-METHODS method_name FOR TABLE FUNCTION table_function_name) and it will implement the data selection using Native SQL code.

CLASS z_amdp_cl_tf DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .

  PUBLIC SECTION.

    INTERFACES if_amdp_marker_hdb.

    CLASS-METHODS get_data FOR TABLE FUNCTION z_table_function_demo.

  PROTECTED SECTION.

  PRIVATE SECTION.

ENDCLASS.

CLASS z_amdp_cl_tf IMPLEMENTATION.

  METHOD get_data BY DATABASE FUNCTION FOR HDB

                          LANGUAGE SQLSCRIPT

                          OPTIONS READ-ONLY

                          USING zgandetails.

    RETURN

           SELECT client,empid,STRING_AGG(qualification,',') as qualification

           from zgandetails

           GROUP BY client,empid;

  ENDMETHOD.

ENDCLASS.

Exposing service

We cannot directly expose table function entity in service definition so we have to create other CDS view with the table function CDS and expose that CDS view in the service definition.

@AbapCatalog.sqlViewName: 'Z_TB_VIEW'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Table function View'

define view z_table_function_view as select from Z_TABLE_FUNCTION_DEMO {

   empid,

   qualification

}