Skip to Content

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

53 Comments
You must be Logged on to comment or reply to a post.
    • 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? ūüėą

      • Now that you can see the documentation, couldn’t I stop blogging? ūüėą

        Noooo. We’re just getting hungry for more details, explanations and examples ūüėČ

      • 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….

  • 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….

  • 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

    • 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.

  • 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

  • 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

      • 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

        • 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.

  • 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.

     

    • 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.

          • 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?

    •  

      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.

    • That¬īs a question ¬†that I can¬īt answer. Maybe you post it as a Question and a SE16 guy sees and answers it.

      Horst

       

       

  • Hello Keller,

    I have a CDS view with input parameter, once it is created(actually generated at run time). I will have a procedure to read the data from it. And from ABAP stack, a table function( I suppose) will be generated when CDS view is called at first time. But I am using procedure to call it so I got an error: Invalidated function: xxxxx. What can I do in this situation? Can I force HANA to genereate the artifacts under the hood at the beginning which means once CDS view with input parameter is generated?

    I think a workaround is just call that CDS view with dummy input parameters by OPEN SQl and then the function will be generated, but I am wondering if there is any formal way.

    Thanks for your help in advance:)

    BR,

    Steve

    • Your assumption is quite wrong.

      A view with parameters is not instantiated as a DB procedure but as a DB view and cannot be called as a procedure.

      • Hi Keller,

        I take some screen shot for my questions:

        1. A CDS view with input parameter has a corresponding function

        2. I cannot select it in my procedure/console because it shows an error: invalidated function

        3. After preview the CDS view with some dummy input parameter, I could select it in console and get the results

        The question is why I will encounter this issue and what I can do to solve it?

        Many thanks.

        BR,

        Steve

      • Have to correct myself.

        The instantiation of a view with parameters depends on the DB platform and can be a function.

        On HANA it is a function.

        • Hi Keller, thanks for the info. Then how can I make the function generated and validated at the very begin when CDS view is generated? BR,Steve

          •  

            SAP does not support this. For reasons like this and some others, it is not recommended to access DB objects in the ABAP database schema directly from the database but only from ABAP via the ABAP DBI (Open SQL or Native SQL).

          • Hi Keller, Actually we create a procedure in HANA layer to read the data from table function. I think that case is valid, isn’t it? So we are still facing this trouble,right? BR,Steve

          • ¬†

            No, that case is not valid.

            The DB objects created by ABAP are created for ABAP. They can depend on settings that are available only when accessed ideally by Open SQL or by SAP¬īs Native SQL. When accessing them from DB ¬†layer, you don¬īt have any control. Examples are the ABAP specific session variables that are evaluated by the DB objects and that are only set correctly when accessed by Open SQL. You must regard the DB objects of ABAP Dictionary including CDS or of AMDP as SAP-internal implementation details. There is no guarantee that this implementation is the same from release to release. E.g., the internal implementation of automatic client handling has changed for CDS views and functions. If you access a view with Open SQL, no problem. But accessing them natively, oh my.

  • Hi Horst,

    Thanks for the hints with the CDS table functions.

    I’m trying to use the table function to read BP details¬†sequential, if partner a is maintained, show a, if not show partner b.

    Therefor I’ve maintained a CDS view with parameters to call the table function.

    Now I’m trying to use this CDS view in my main CDS view. Do you have a hint how to implement this?

    Unfortunately, this does NOT work.

    Cheers, Claudia

    •  

      See  https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abencds_f1_select_parameters.htm

      You can’t specify columns as actual parameters. You put a colon in front of hlink_dat.guid_set, but this is not make it a parameter.

      Why do you wrap the table function in a view? You can use it in the main view directly.

      You can use an CDS entity as a data source in a join, but you can’t use columns as parameters. You can use columns in the ON condition for the result set of the table function.

      Wouldn’t a case expression better suite your use case?

      • Hi Horst,

        Thanks for the hint with the case, I’ve adopted¬†the cds view.

        But I still have a second requirement, where I need to first loop over the BP hierarchy and then select the marketing attribute, where I would like to use a table function. Even if I directly embed the table function into the main view, I need to pass the parameter (customer id), but I cannot access the columns as parameters, could I?

        Cheers, Claudia

        •  

          Hi Claudia,

          No, you can’t pass columns as parameters. In fact how should that work? You’re inside a view,¬†how to pass a column value¬†to a function that returns a tabular result? There’s¬† not a loop concept in CDS views.

          For looping you might either create a “main” table function and loop in SAPScript or use Open SQL in ABAP, where you can SELECT from CDS table functions and can pass ABAP variables as parameters.

          Horst

           

        • Hello Claudia,

          Looping over BP hierarchies is not always needed as HANA provides such an object like hierarchy view, optimized for usage of hierarchies.

          On HANA 1, the syntax looks like:¬†CREATE COLUMN VIEW <ZHIE1> TYPE HIERARCHY WITH PARAMETERS ( … But better to use HANA Studio to model this. There were some guides in the internet…

          On HANA 2, the things are a bit different. There are Hierarchy Functions. A bit more powerful and a bit more standard approach.

          The benefit of hierarchies is the set of predefined standard functions or expressions.

          E.g.: SELECT * FROM <ZHIE1>(“expression” => ‘ascendants(“123456”)’) ORDER BY level

          will return a set of ascendants for the node 123456, etc.

          If, at certain, point of time you would need to join your hierarchy and “unpack” it, check this post:

          https://answers.sap.com/questions/283947/how-to-unpack-hana-hierarchy-view-to-a-more-useful.html

          Alternatively you can build a function or procedure, which would combine a complete logic you want to implement. Or just put certain things onto ABAP layer, but to not use LOOPs, only one call. You can pass a table of partner guids into  AMDP via TT: BUP_PARTNERGUID_T.

          BR, Dima

  • HI Horst,

    Is it possible to implement CDS table function via database proxy procedure?

    What option do I have if I need to call a database stored procedure (or say HANA table function) inside an ABAP-CDS entity?

    If I understand correctly, with AMDP we need to write the entire script in ABAP layer. If I need to re-use pre-existing database procedures (instead of writing new via AMDP), what options do I have?

    Thanks,

    Anup

  •  

    Hi Horst,

    My system Netweaver 7.5 SP7, HANA 1.0 SPS 12, BO 4.2 SP3, Analysis for Office Version 2.3.0.57241

    I have created an AMDP that I would like to consume either directly or indirectly from one of the Business Object front end tools (WEBI, Analysis for Office, etc). How do I accomplish this? I have tried the following:

    • WEBI or Analysis for Office consumes Calculation View (script, not graphical) consumes AMDP
      • This works, but I need input parameters that accept multiple entries. Multiple entries are only supported for graphical calculation view.
    • CDS view table function implemented by method AMDP.
      • This works as a CDS view, but it does not seem to be available when I search for it as a datasource in analysis for office or any other BO front end tool. I used Annotation – @Analytics:{dataCategory:#CUBE} in the CDS view definition.
      • I have connected a normal CDS view (not defined as table function) as a datasource in analysis for office using Annotation – @Analytics:{dataCategory:#CUBE}. It works fine.
    • NOTE: the AMDP imports values and exports a data table. It performs read only operations. It does not update data.

    What is the recommended approach for consuming an AMDP with BO front end tools?

    Thanks for any help,

    Mike

    • I’m not an expert in these frameworks and don’t know which CDS entities they support, but wrap the¬† CDS table function as data source of a CDS view, if only views are supported?

  • If I define a table function Z_FUNC1 implemented via ZCL_FUNC1=>get_func1
    can’t i then create another function Z_FUNC2 implemeted via ZCL_FUNC2=>get_func2

    METHOD get_func2 BY DATABASE PROCEDURE FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING Z_FUNC1

    This is giving me the error:
    Views with AMDP implementations are not supported.

    But also

    METHOD get_func2 BY DATABASE PROCEDURE FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING ZCL_FUNC1=>get_func1

    Is giving me another error:

    The user declaration for the object “ZCL_FUNC1=>z_func1” is not needed.

  • I would like to access data from ABAP in a different schema on the same HANA database.¬† This will be used to drive a UI that needs to have capabilities like fuzzy search.

    If I have to retrieve the data using a CDS table function/AMDP pairing and I need to use annotations like @Search.fuzzinessThreshold, will this involve the database copying all the data from the other schema before performing the search?  The ADMP itself appears to return everything rather than passing the restrictions down (with the exception of parameters).

  • Hello,

    is it possible to use correlated subquery in CDS like in ABAP. We would replace ABAP statement with correlated subquery in OData service implementation by OData based on CDS but I cannot find solution with similar performance.

    ABAP (old)

    select * from A as t1 where date = ( select MAX(date) from A as t2 where t2~key = t1~key )

    CDS (new) 2 steps below – which is slow:

    1. CDS_A

    select key MAX(date) from table A group by key

    2. CDS

    select * from A as t1 inner join CDS_A as t2 on t1~key = t2~key and t1~date = t2~date

    thanks

  • Horst Keller Thanks for the informative Blog !

    Have a clarification. In AMDP class, we can have multiple Exporting Table Result Sets. Whereas as far as I tried, CDS Table Functions can have only single Return Parameter that too the Structure can be defined only with type Data Elements. Would be great to know if there is any workaround to return multiple Tables from CDS Table Functions.

    Thanks in Advance !