I am excited to share my first blog article with you.
I have participated in a project that includes a complex CDS structure and lots of AMDP classes recently. We have used lots of functions like ROW_NUMBER(), RANK() and DENSE_RANK(). I wanted to explain how these functions work and mention useful pin points for other people that are new to ABAP, like myself. I have kept the examples simple for beginners to understand better and use these as templates for more complex scenarios.
First of all, I will explain the concepts of CDS, Table Function and AMDP as well as their relationships with each other.
CDS (Core Data Services):
• It is the upgraded version of ‘view’ in ABAP.
• It can be created to read and process the data in the database layer.
• CDS is designed for a singular logic set, producing only one result set, so a debugger can not be put in it.
• Defined in ABAP repository using SQL DDL syntax.
• Use open SQL language.
AMDP (ABAP Managed Database Procedures):
• AMDP is a class that allows us to write the Object Oriented classes at back-end for the views mentioned above.
• It is a simple ABAP class method.
• It is able to have multiple result returns on complex logic.
• It can process and modify the data on the database layer.
• Debugger can be put inside.
• Use native SQL language.
Why we have the need to consume AMDP in CDS?
When database requires specific functions that open SQL does not include, then we need to consume AMDP in CDS.
For example, in this case, if it needs us to perform complex calculations in CDS, sorting or deleting multiplied data, we can achieve this with AMDP.
How do we create the communication between CDS and AMDP?
Table Function
• It is used to represent a specific function in CDS and called within AMDP or CDS views.
• By using CDS and AMDP, more flexible and higher performance solutions can be achieved both in the subjects of data modelling and database operations.
• Natively implemented on the database.
To schematize the relationship between CDS, AMDP and Table Function simply;
Let’s talk about the ROW_NUMBER, RANK and DENSE_RANK functions if we are familiar with the concepts now. Since I think that these functions can be understood better with examples, let’s start by creating a simple example.
1. Creating CDS
First we start with creating a CDS which will consume our Table Function.
By clicking right to our package, choose the option new -> other ABAP Repository Object and Choose data definition.
In our scenario, we are taking our data from the table that includes the production place and products made in there.
@AbapCatalog.sqlViewName: 'ZDEMOPRODUCTION'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Production Place Products And Costs View'
define view ZDEMO_PRODUCTION_CDS
as select from zdm_producion_iy
{
key production_place_id as ProductionPlaceId,
key product_id as ProductId,
production_place_text as ProductionPlaceText
}
2. Creating Table Function
•RETURNS: These are the set of fields that we want to review in our CDS and will return from our AMDP class.
•@Environment.systemField : #CLIENT : It is given the current client ID implicitly by the Open SQL statement SELECT and can be used to restrict the results set in the Native SQL implementation of the function.
( https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-us/abencds_f1_parameter_annotations.htm )
•At this point we should call our AMDP class. (AMDP gets active before creating the class too.)
•IF_AMDP_MARKER_HDP: It is used to making ABAP classes compatible with SAP HANA database features and performing some special calculations.
•We need to specify the table function we will use at this point.
( abap-cheat-sheets/12_AMDP.md at main · SAP-samples/abap-cheat-sheets · GitHub ABAP Keyword Document... )
•We have created out CDS, Table Function and AMDP Class. Now I want to give some examples about the functions I have mentioned at the beginning of my blog.
•Let’s assume we have a table that has a product id, a product name, production quantity, a purchase price (EUR), a selling price (EUR), a production start date and production end date.
ROW_NUMBER:
•It indicates a row number for every row on the table.
•OVER( ORDER BY <X> DESC/ASC) Clause: It is the order of ‘sort variable <X> as decreasing/ascending and give a line number for the sorted variable <X> ’
•Let’s do this to the product quantity in our example.
CLASS zdemo_products_cl IMPLEMENTATION.
METHOD get_products BY DATABASE FUNCTION FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING zdm_products_iy.
t_main = select z.mandt,
z.product_id,
z.product_name,
LTRIM( z.product_quantity, '0' ) as product_quantity,
LTRIM( z.purchase_price, '0' ) as purchase_price,
LTRIM( z.selling_price, '0' ) as selling_price,
z.production_start_date,
z.production_end_date
from zdm_products_iy as z
where z.mandt = clnt;
t_row_number = select
row_number( ) over ( order by t.product_quantity asc ) as row_number,
t.*
from :t_main as t;
return select mandt,
product_id,
product_name,
product_quantity,
purchase_price,
selling_price,
production_start_date,
production_end_date,
row_number
from :t_row_number;
endmethod.
ENDCLASS.
OR:
t_main = select z.mandt,
z.product_id,
z.product_name,
LTRIM( z.product_quantity, '0' ) as product_quantity,
LTRIM( z.purchase_price, '0' ) as purchase_price,
LTRIM( z.selling_price, '0' ) as selling_price,
z.production_start_date,
z.production_end_date,
ROW_NUMBER ( ) OVER( ORDER BY "PRODUCT_QUANTITY" asc ) AS "ROW_NUMBER"
from zdm_products_iy as z
where z.mandt = clnt;
return select mandt,
product_id,
product_name,
product_quantity,
purchase_price,
selling_price,
production_start_date,
production_end_date,
row_number
from :t_main;
Note: We deleted the zeros with LTRIM function.
We can anaylize the result by running the Table Function.
But what if we have two arragment criteria?
•To give an example if we want to arrange 008(wardrobe) and 004(frame) according to product quantity primarily and then according to production id;
•We should arrange our code like showed below;
t_row_number = select
row_number( ) over ( order by t.product_quantity asc , t.product_id asc ) as row_number,
t.*
from :t_main as t;
The first value we wrote in Order By is the priority value. We should write the values inside the Order By in accordance to the priority order we want.
ROW_NUMBER () function using PARTITION BY Clause:
•The field we wrote inside Partition BY gives a different row number to the every row. If there are more records for the same data, ORDER BY() continues increasingly or decreasingly in accordance to the value we wrote.
t_row_number = select
row_number( ) over ( PARTITION BY t.product_quantity ORDER BY t.product_id asc ) as row_number_partion_by,
t.*
from :t_main as t;
OR:
t_main = select z.mandt,
z.product_id,
z.product_name,
LTRIM( z.product_quantity, '0' ) as product_quantity,
LTRIM( z.purchase_price, '0' ) as purchase_price,
LTRIM( z.selling_price, '0' ) as selling_price,
z.production_start_date,
z.production_end_date,
ROW_NUMBER ( ) OVER( PARTITION BY "PRODUCT_QUANTITY" ) AS "ROW_NUMBER_PARTION_BY"
from zdm_products_iy as z
where z.mandt = clnt;
return select mandt,
product_id,
product_name,
product_quantity,
purchase_price,
selling_price,
production_start_date,
production_end_date,
row_number_partion_by
from :t_main;
A USEFUL TRICK: When our data duplicates, we can eliminate the duplicating data by writing return row_number = ‘1’ and showcasing the every unique row. For example let’s assume that we want to showcase the every unique production quantity row.
t_row_number = select
row_number( ) over ( PARTITION BY t.product_quantity ORDER BY t.product_id asc ) as row_number_partion_by,
t.*
from :t_main as t;
return select mandt,
product_id,
product_name,
product_quantity,
purchase_price,
selling_price,
production_start_date,
production_end_date,
row_number_partion_by
from :t_row_number
where row_number_partion_by = 1;
OR:
t_main = select z.mandt,
z.product_id,
z.product_name,
LTRIM( z.product_quantity, '0' ) as product_quantity,
LTRIM( z.purchase_price, '0' ) as purchase_price,
LTRIM( z.selling_price, '0' ) as selling_price,
z.production_start_date,
z.production_end_date,
ROW_NUMBER ( ) OVER( PARTITION BY "PRODUCT_QUANTITY" ) AS "ROW_NUMBER_PARTION_BY"
from zdm_products_iy as z
where z.mandt = clnt;
return select mandt,
product_id,
product_name,
product_quantity,
purchase_price,
selling_price,
production_start_date,
production_end_date,
row_number_partion_by
from :t_main
where row_number_partion_by = 1;
RANK:
•It gives a different number for every different row. If there is multiple identical rows, it gives the same row number to them. Then skips the row as it has given a different number to the row and continues as normal. Let’s analyse the example to understand better.
t_row_number = select
RANK ( ) OVER( ORDER BY product_quantity asc ) as rank,
t.*
from :t_main as t;
OR:
t_main = select z.mandt,
z.product_id,
z.product_name,
LTRIM( z.product_quantity, '0' ) as product_quantity,
LTRIM( z.purchase_price, '0' ) as purchase_price,
LTRIM( z.selling_price, '0' ) as selling_price,
z.production_start_date,
z.production_end_date,
RANK( ) OVER( ORDER BY "PRODUCT_QUANTITY" asc ) AS "RANK",
from zdm_products_iy as z
where z.mandt = clnt;
return select mandt,
product_id,
product_name,
product_quantity,
purchase_price,
selling_price,
production_start_date,
production_end_date,
rank
from :t_main;
•Product quantity of 008(wardrobe) and 004(frame) are the same. Rank function gives the same row number to both because of it’s feature. Then continues as if it matched the 004(frame) with the number for.
DENSE_RANK():
•It gives different numbers to every different row. It gives the same number if two rows are the same but differently from the RANK function, it continues assigning numbers to the next rows without skipping a number for the row it gave the same number to.
t_row_number = select
DENSE_RANK ( ) OVER( ORDER BY product_quantity asc ) as dense_rank,
t.*
from :t_main as t;
OR:
t_main = select z.mandt,
z.product_id,
z.product_name,
LTRIM( z.product_quantity, '0' ) as product_quantity,
LTRIM( z.purchase_price, '0' ) as purchase_price,
LTRIM( z.selling_price, '0' ) as selling_price,
z.production_start_date,
z.production_end_date,
DENSE_RANK ( ) OVER( ORDER BY "PRODUCT_QUANTITY" asc ) AS "DENSE_RANK"
from zdm_products_iy as z
where z.mandt = clnt;
return select mandt,
product_id,
product_name,
product_quantity,
purchase_price,
selling_price,
production_start_date,
production_end_date,
dense_rank
from :t_main;
•To understand the difference better, I would like to showcase both functions in a way we can see those side by side.
CLASS zdemo_products_cl IMPLEMENTATION.
METHOD get_products BY DATABASE FUNCTION FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING zdm_products_iy.
t_main = select z.mandt,
z.product_id,
z.product_name,
LTRIM( z.product_quantity, '0' ) as product_quantity,
LTRIM( z.purchase_price, '0' ) as purchase_price,
LTRIM( z.selling_price, '0' ) as selling_price,
z.production_start_date,
z.production_end_date,
ROW_NUMBER ( ) OVER( ORDER BY "PRODUCT_QUANTITY" asc ) AS "ROW_NUMBER",
RANK( ) OVER( ORDER BY "PRODUCT_QUANTITY" asc ) AS "RANK",
DENSE_RANK ( ) OVER( ORDER BY "PRODUCT_QUANTITY" asc ) AS "DENSE_RANK"
from zdm_products_iy as z
where z.mandt = clnt;
return select mandt,
product_id,
product_name,
product_quantity,
purchase_price,
selling_price,
production_start_date,
production_end_date,
row_number,
rank,
dense_rank
from :t_main;
endmethod.
ENDCLASS.
4.Calling Table Function Through CDS:
@AbapCatalog.sqlViewName: 'ZDEMOPRODUCTION'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Production Place Products And Costs View'
define view ZDEMO_PRODUCTION_CDS
as select from zdm_producion_iy as m
inner join ZDEMO_PRODUCTS_TABLE_FUNCTION(clnt : $session.client) as t on m.product_id = t.product_id
{
key m.product_id as ProductId,
key m.production_place_id as ProductionPlaceId,
m.production_place_text as ProductionPlace,
t.product_name as ProductName,
t.product_quantity as ProductQuantity,
t.purchase_price as PurchasePrc,
t.selling_price as SellingPrc,
t.production_start_date as StartDate,
t.production_end_date as EndDate,
t.row_number,
t.rank,
t.dense_rank
}
The functions I mentioned were our most commonly used functions with AMDP in our project. With this, we came to the conclusion of my blog article I wrote to explain CDS, Table Function and AMDP simply as well as understanding the differences between them. I hope it was successfully explained for beginners and also was a well made template for the ones who just recently began their ABAP journey already.
I wish all of you a good day.
Some useful links:
ABAP Keyword Documentation (sap.com)
ABAP News for Release 7.50 – CDS Table Functions Implemented by AMDP | SAP Blogs
Cheat Sheet CDS ABAP (brandeis.de)
- SAP Managed Tags:
8 Comments
| User | Count |
|---|---|
| 4 | |
| 4 | |
| 3 | |
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
