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

To report this post you need to login first.

28 Comments

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

    1. Horst Keller Post author

      In fact I succeeded in getting the docmentation pushed to the help portal last night. Should have been there since RTC, but  ran into some naming convention problems regarding the URL  …

      Now that you can see the documentation, couldn’t I stop blogging? 😈

      (0) 
      1. Peter Inotai

        Now that you can see the documentation, couldn’t I stop blogging? 😈

        Noooo. We’re just getting hungry for more details, explanations and examples 😉

        (0) 
      2. Shai Sinai

        Well,

        For the matter of fact, I couldn’t wait for your upcoming posts and have already read the changes documentation (briefly).

        There are many exciting new features out there….

        (0) 
  1. Mohit Chopra

    Hello Horst,

    I have some queries regarding Table functions: Will be really appreciable if you can help:

    1. How does authorization concept work with table functions?can we create DCL roles for them?

    2. Can we create ODATA service or Analytical query using annotations for View created via Table Functions?

    3. What is the advantage of using these Table functions over Standalone AMDP functionality? We can access Data Functions using AMDP without invloving CDS..What extra does Table function provide??

    Really looking forward for these answers….

    (0) 
    1. Horst Keller Post author

      1, no you must wrap their usage in an interface view

      2, you can write any annotation everywhere, it depends on the frameworks that analyze them, if they support the direct usage of table functions. Please refer to the corresponding documentation

      3, To incorporate them in your data model (DDL!)

      (0) 
      1. Ramesh Raja R

        Hi Horst,

        Is Table function faster than an AMDP procedure. In case I am having the same logic within both…In which case i should go for Table function and which case I should go for a AMDP procedure?

        Thanks,

        Ramesh

        (0) 
        1. Horst Keller Post author

          Each ABAP CDS table function is implemented by an AMDP procedure. Therefore the execution time is the same if you have the same logic in both.

          You go for table functions if you want to use them as data sources in other CDS entitties. You go for normal AMDP procedures if you want to call them from ABAP via meth( ).

          Horst

          (0) 
          1. Jonathan Choi

            I have added the analytical annotation but in BO Design Studio, it doesn’t show up as transient data source.  Would you give us some example?

             

             

            (0) 
  2. Saubhagya Nayak

    Hi Horst,

    Thanks for posting the article. It’s very helpful and informative.

    I’m trying to implement a table function but getting an issue. Hopefully you can answer this.

    We have some non-operational data in S4HANA which we have stored in a separate schema. We are trying to expose that data in reporting layer, so we are trying to create some CDS views on top of that datasets. Since we can’t access cross schema tables in CDS views, so we are using table functions to do that. But in table function class, when I specify the schema_name.table_name it says the table is invalid. so how can I reference the table in the class implementation?

    Attached is a screenshot of my class. Custorderline is the table which is stored under the Analytics_Demo schema.

    CDS Table Functions.PNG

    Regards

    Saub

    (0) 
    1. Horst Keller Post author

      Pls. read the documentation for USING. You specify dictionary tables behind USING, not those from other schemas. These can be used without being listed behind USING.

      (0) 
  3. Gaurav Kumar

    Hi,

    My table name start from ‘/’ because of this I am getting an error:

    SQLSCRIPT: sql syntax error: incorrect syntax near “/”

    For example, my table name is: /ABCD/T_XYZ.
    Please let me know how this can be fixed.

    Regards,
    Gaurav

    (0) 
  4. Alex Svobodin

    Horst,

    Thank you for your original article. Is it possible to call pure ABAP class->method from either within a CDS view or an AMDP method? We have some sophisticated ABAP in a regular class->method that returns exactly what we need to pass back to a CDS view. With AMDP we are in the SQL script world and re-writing the ABAP into SQL script to be utilized as a CDS table function will take quite abit of time. We are on 7.5 SP05.

    Thank you,
    Alex

    (0) 
      1. Alex Svobodin

        Horst,

        I understand it’s wishful thinking that SQL script on the dbase layer would talk to ABAP on the app layer, it’s just that in our case re-writing ABAP into SQL is very time-consuming at this point. And i thought a table function could come to rescue as we are already in the familiar OO environment, though the AMDP class/method are very specific.

        So is it correct to assume that anything related to CDSes today can only be extended via the SQL script via the AMDP? ABAP doesn’t come close into the CDS framework anywhere?

        Thank you

        (0) 
        1. Horst Keller Post author

          ABAP CDS entities are under discussion. Those would be accessed like other CDS entities but executed in ABAP. But I wouldn’t expect that you can use them in or join them with SQL views.

          (0) 
  5. Jonathan Choi

    Hi, I’m getting following error while trying your tutorial.  I have found OSS note, 2289860, for BASIS 750 release and the SP has been applied.  Currently I am using the BASIS 751 release so I’m checking to see if relevant OSS note for this error is available for BASIS 751 release.  Thanks.

     

    (0) 
    1. Horst Keller Post author

      The relationship between CDS table function and implementing method is one to one. You cannot use my implementation in your CDS table function. This is also clearly expressed by the syntax pair IMPLEMENTED BY <-> FOR TABLE FUNCTION.

      (0) 
          1. Jonathan Choi

            Thanks.

            One more dire question, using CDS view for CRUD operation has issue of not being able to incorporate enhancement framework.  Another issue is using view is a direct table read/update which we should never do for SAP tables, due to thousands of table being accessed and updated.  How can these problems be avoided and remedied?

            (0) 
    1. Horst Keller Post author

       

      I don’t think so. You need return values for that purpose. AMDP-functions have been introduced primarily to serve as implementations of CDS table functions.

      (0) 

Leave a Reply