Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
dmitry_kuznetsov1
Active Participant
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
7 Comments
Labels in this area