How to use table functions with CDS views?

In this short tutorial, I want to showcase you, how to use table functions. Table functions allows you to simply define a CDS structure and later a view to be consumed by other CDS views or custom ABAP logic. The underlaying data acquisition differs from the normal CDS view entities. It can refer to a custom static ABAP method, which can contain a custom SQLScript logic. It returns then an internal table, based on the CDS structure.

First of all, we need to create a new CDS view with a template based on table function. For that we name our CDS view “ZI_CONNECTIONS”. Then we select as template the “Define Table Function with Parameters”.

Then we define the fields for this object:

@EndUserText.label: 'Connections with table function'
define table function ZI_CONNECTIONS
  with parameters
    @Environment.systemField: #CLIENT
    clnt :abap.clnt
returns
{
  mandt       : abap.clnt;
  carr_id     : zcarr_id;
  conn_id     : zconn_id;
  flight_date : abap.dats;
  flight_time : abap.tims;
}
implemented by method
  ZCL_ZI_CONNECTIONS=>get_connections;

Now we can implement a new ABAP class, which contains then our SQLScript logic. For that we need inside this class a new static method, which has some few extensions on it.

CLASS zcl_zi_connections DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    CLASS-METHODS: get_connections FOR TABLE FUNCTION zi_connections.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS zcl_zi_connections IMPLEMENTATION.

  METHOD get_connections BY DATABASE FUNCTION
    FOR HDB
    LANGUAGE SQLSCRIPT
    USING zr_connections.
    RETURN select mandt, carrid as carr_id, connid as conn_id FROM zr_connections WHERE mandt = :clnt.
  ENDMETHOD.
  
ENDCLASS.

This approach allows you to use a client specific table since we provide the client as a parameter from the CDS table function. This parameter is available in SQLScript inside the AMDP method with :clnt.