Skip to Content
Author's profile photo Dmitry Kuznetsov

Select TOP N / UP TO N rows / ORDER BY in a ABAP CDS

In this post I discuss a workaround if you need a TOP N / UP TO N rows / ORDER BY in a CDS-based modeling. Unfortunately these clauses are missing in CDS views and, therefore can only be implemented by CDS Table Functions. Hopefully, it is temporary 😉

The solution is as follows: CDS Table Function calls a method in an ABAP class, this method executes a SQLscript on HANA DB and returns the data back to the CDS. From that point on, you can use it in further modeling, i.e. other CDS views, filling derived parameters, etc. The binding parts are highlighted in the same color on both CDS and CLASS sides. Also, important sections, specific to AMDP are commented respectively.

CDS Table function Class and Method for AMDP
   

 

Procedure:

 

1.      Create a CDS table function with expected output definition, pointing to a dummy class & dummy method.

 

@EndUserText.label: 'Budget Versions'
define table function ZWG_I_BUDGETVERSIONS
with parameters 
    @Environment.systemField: #CLIENT 
    clnt            : abap.clnt
    returns {
      rclnt         : abap.clnt;
      ryear         : fis_ryear;
      rzzversion    : GENFM_EXTBR_DEFAULT_VER;
  }
implemented by method class_name=>method_name;

2.      Create a proper class and method. Code example attached

class ZCL_AMDP_FOR_REPORTING definition
  public
  final
  create public .

public section.
* this INTERFACES part is needed for AMDP to work
    INTERFACES if_amdp_marker_hdb.
    CLASS-METHODS ZM_GET_LAST2_BUDGET_VERSIONS
* the following line is the binding to my CDS, so I do not need to define any types
    FOR TABLE FUNCTION ZWG_I_BUDGETVERSIONS.
private section.
ENDCLASS.

CLASS ZCL_AMDP_FOR_REPORTING IMPLEMENTATION.

  METHOD ZM_GET_LAST2_BUDGET_VERSIONS
* this section is mandatory for HANA-AMDP's to work
    BY DATABASE FUNCTION
    FOR HDB LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
* end of the HANA-specific section
    USING ZZWGXA.
      RETURN
-- this part is an SQL script executed on HANA
            select top 2 distinct rclnt, ryear, rzzversion
            from ZZWGXA
            order by concat(ryear, rzzversion) desc;
-- end of the HANA script
     ENDMETHOD.

ENDCLASS.

3.      Finalize the CDS table function to point to the right class & method. Final code example attached

 

// This CDS Table Function is needed because a CDS view
// has no possibility to select top N records and ORDER BY
// so we do it in a respective AMDP function

@EndUserText.label: 'Budget Versions'
define table function ZWG_I_BUDGETVERSIONS
with parameters 
    @Environment.systemField: #CLIENT 
    clnt            : abap.clnt
    returns {
      rclnt         : abap.clnt;
      ryear         : fis_ryear;
      rzzversion    : GENFM_EXTBR_DEFAULT_VER;
  }
// the next section is a binding to CLASS and a METHOD for AMDP
implemented by method ZCL_AMDP_FOR_REPORTING=>ZM_GET_LAST2_BUDGET_VERSIONS;

 

This solution has been inspired by:

SAP training S4D430 Building Views in Core Data Services ABAP (CDS ABAP), Unit 5

This help page https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abenamdp_functions_abexa.htm

Examples provided in the package SABAPDEMOS, namely class cl_demo_amdp_functions and CDS DEMO_CDS_GET_SCARR_SPFLI

Assigned tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michael Keller
      Michael Keller

      Thanks for sharing. Funny coincidence: I’m actual visiting SAP training S4D430 ?

      Best regards

      Michael

       

      Author's profile photo Dmitry Kuznetsov
      Dmitry Kuznetsov
      Blog Post Author

      Nice! I sometimes teach that course, too. Hope you enjoyed it.

       

      Author's profile photo Premiga Loganathan
      Premiga Loganathan

       

      Hi

      Instead of top 2 entries, I need to pass input parameter ( TOP N)  from cds view based on the selection prompt values, please suggest any idea

      Author's profile photo Ankur Gokhale
      Ankur Gokhale

      CDS lacks many statements, TOP n, subquery, select single, limit. Its really ODD to write a class, attaching the same to CDS function table just to get a single record. Why it cant just support LIMIT or SINGLE statement?

      If it requires to write Open SQL in a class, then why not to drop cds and write open sql in odata data provider class? Much simple, isnt it?

      Author's profile photo Dmitry Kuznetsov
      Dmitry Kuznetsov

      well, CDS offers quite more: push-down of execution to HANA, DCL Access Controls down at DB, tons of flex without creating big internal tables, flexibility of layered modeling, etc. So, I take CDS as much as I can, but make your choice.

      Author's profile photo Praseeda Aramadaka
      Praseeda Aramadaka

       

      Hi

      In CDS does the ORDER BY work??? In table function output, the sorting works but when this is consumed in CDS it  isn't working. Please suggest.

      Author's profile photo Marc-Holger Koop
      Marc-Holger Koop

      Hi Praseeda Aramadaka ,

      not sure if your question still remains, no, there is no ORDER BY available in CDS, as the 'philosophy' behind it is that the respective consumer should decide how the data should be sorted.

      However, to provide a default sorting, you can use @UI.presentationVariant in your Consumption View (where generally you put all the UI-Annotations). You need something like this before the DEFINE VIEW statement:

      @UI.presentationVariant: [{
              sortOrder: [{ by: 'BeginDate', direction: #ASC }]
      }]

      define view <MyView>... blablabla...

      Hope it helps and best regards,
      Marc