ABAP News for Release 7.50 – CDS Table Functions Implemented by AMDP

I just started with blogging about important ABAP News for ABAP 7.50 and – whoosh – I am asked for CDS news. OK then, a blog about the new CDS table functions (but hey, I have also real work to do).

ABAP CDS is the ABAP-specific implementation of SAP’s general Core Data Services (CDS) concept. ABAP CDS is open, meaning that you can use it on all database platforms supported by SAP. And yes, CDS views with parameters, introduced with ABAP 7.40, SP08, are supported by all databases with ABAP 7.50.

While openess has its merits, developers working only on the HANA platform might miss some code-push-down capabilities in ABAP CDS. One of these missing capabilities was the usage of database functions in data models built with CDS. Up to now, only CDS views were available. With ABAP 7.50 ABAP CDS also supports CDS table functions as CDS entities. Two problems had to be solved:

• how to make table functions that are implemented natively on the database callable in CDS
• how to manage the life cycle of native table functions to be constantly available to a data model built on the application server

Two questions, one answer: ABAP Managed Database Procedures (AMDP), introduced with ABAP 7.40, SP05. AMDP is a class-based framework for managing and calling stored procedures as AMDP procedures in AS ABAP. For the time being, AMDP is supported by the HANA platform only. Before ABAP 7.50, AMDP knew only database procedures without a return value. With ABAP 7.50, AMDP supports also database functions with a tabular return value. And the main purpose of these AMDP-functions is the implementation of CDS table functions. They cannot be called as functional methods in ABAP, while AMDP-procedures can be called as ABAP methods.

In order to create a CDS table function, you have two things to do:

• define it in a CDS DDL source code,
• implement it in an AMDP method with a  return value.

Both steps are possible in ADT (Eclipse) only.

The definition in CDS DDL is straight forward, as e.g.:

@ClientDependent: true
define table function DEMO_CDS_GET_SCARR_SPFLI_INPCL
  with parameters @Environment.systemField: #CLIENT
                  clnt:abap.clnt,
                  carrid:s_carr_id
  returns { client:s_mandt; 
            carrname:s_carrname;
            connid:s_conn_id;
            cityfrom:s_from_cit;
            cityto:s_to_city; }
  implemented by method
    CL_DEMO_AMDP_FUNCTIONS_INPCL=>GET_SCARR_SPFLI_FOR_CDS;

A CDS table function has input parameters and returns a tabular result set, that is structured as defined behind returns. You see, that the annotation @ClientDependent can be used to switch on an automatic client handling for Open SQL. You also see a new parameter annotation @Environment.systemField, also available for views, that is handled by Open SQL by implicitly passing the value of sy-mandt to that parameter. Such a CDS table function is a fully fledged CDS entity in the ABAP CDS world and can be used like a CDS view: It is a global structured data type in the ABAP Dictionary and it can be used as data source in Open SQL’s SELECT and in CDS views. Behind implemented by method you see the AMDP class and method where the function has to be implemented in.

After activating the CDS table function you can go on implement the functional AMDP method in an AMDP class, that is a class with the marker interface IF_AMDP_MARKER_HDB. An AMDP method for a CDS table function must be a static functional method of a static AMDP class that is declared as follows:

CLASS-METHODS get_scarr_spfli_for_cds

              FOR TABLE FUNCTION demo_cds_get_scarr_spfli_inpcl.

The declaration is linked directly to the CDS table function. The parameter interface is implicitly derived from the table function’s definition! Implementation looks like you might expect it:

  METHOD get_scarr_spfli_for_cds
        BY DATABASE FUNCTION FOR HDB
        LANGUAGE SQLSCRIPT
        OPTIONS READ-ONLY
        USING scarr spfli.
    RETURN SELECT sc.mandt as client,
                  sc.carrname, sp.connid, sp.cityfrom, sp.cityto
                  FROM scarr AS sc
                    INNER JOIN spfli AS sp ON sc.mandt = sp.mandt AND
                                              sc.carrid = sp.carrid
                    WHERE sp.mandt = :clnt AND
                          sp.carrid = :carrid
                    ORDER BY sc.mandt, sc.carrname, sp.connid;
  ENDMETHOD.

Nothing really new but BY DATABASE FUNCTION and that  READ-ONLY is a must. Implementation is done in native SQLScript for a HANA database function. And native means, you have to take care for the client. Automatic client handling is done on the Open SQL side only. Of course, a real CDS table function would do more HANA specific things (e.g. wrapping a HANA function) than a simple join as shown in the simple example here! A join you can code also in Open SQL or in a CDS View.

Speaking about Open SQL, last but not least, the usage of our CDS table function as data source of SELECT in an ABAP program:

  SELECT *
        FROM demo_cds_get_scarr_spfli_inpcl( carrid = @carrid )
        INTO TABLE @DATA(result)
        ##db_feature_mode[amdp_table_function].

Not different to an access of a CDS view with parameters. But you must switch off a syntax warning with a pragma to show that you are sure what you are doing, namely coding for HANA only.

Note that we don’t need to pass the client explicitly. This is because the according parameter was annotated for implicit passing of the respective system field. Since the CDS table function was annotated as client dependent, the result set of Open SQL’s SELECT does not contain a client column – as it is for CDS views. Furthermore all lines of the result set, that do not belong to the current client are implicitly removed. That’s why the return list of a client dependent table function must have a client column.  For the sake of performance, the native implementation should deliver only lines of the current client. But since it is native it has to take care for that itself. Confusing? That’s when open and native meet. In ABAP, normally the DBI does this handling for you. But this is not possible here.

For more information see

• CDS Table Functions
• AMDP Function Definitions