Introduction
This blog post discusses the usage of the CDS views, CDS Table Functions and AMDP methods with code push down approach to do calculations and write business logic at database level instead of application server level.
Here I have shared one scenario where the business requirement is to find the list of equipment with it's parent equipment and the lop level equipment.
As we know that parent equipment can be easily found in EQUZ-HEQUI but for getting its top level equipment it requires little calculation and logic.
Now for doing these kinds of calculations and logic at database level we have AMDP methods where we can write using SQL code and achieve our desired output.
You can refer below link for basic understanding of CDS Table Function
CDS Development User Guid
Requirement
Business requirement is to get the list of equipment with its parent equipment and top level equipment using CDS views
Now as we know the parent equipment can be easily found in table EQUZ field HEQUI, but the top level equipment is nowhere present in any database table
So if we want to achieve this functionality using ABAP then it will be a piece of cake for any ABAPer using LOOP and SELECT statements.
But to achieve this using CDS views we need to explore AMDP feature and write calculation and logic part for top level equipment.
Given equipment hierarchy structure is somewhat like
Equipment Hierarchy
Required final output table should be as below
| Equipment | Parent_Equipment | Top_Level_Equipment |
| 10000028 | ||
| 10000029 | 10000028 | 10000028 |
| 10000030 | 10000029 | 10000028 |
| 10000031 | 10000029 | 10000028 |
| 10000032 | 10000029 | 10000028 |
Approach
So Let’s begin.
Step 1 – Create CDS Table Function
Create CDS Table Function name zi_equi_hier_1
Input will be mandt and output will be mandt, equipment, parent equipment and top level equipment.
implemented by method zcl_get_hier=>get_top_hier;
@EndUserText.label: 'Equipment Hierarchy'
define table function zi_equi_hier_1
with parameters
@Environment.systemField: #CLIENT
mandt:abap.clnt
returns {
mandt : mandt;
equnr : equnr; // Equipment
pequi : equnr; // Parent Equipment
hequi : equnr; // Top Level Equipment
}
implemented by method zcl_get_hier=>get_top_hier;
Step 2 – Implement the Table Function method AMDP
Define class ZCL_GET_HIER
INTERFACES IF_AMDP_MARKER_HDB is must to use when defining as an AMDP procedure
Create class-method GET_TOP_HIER for table function zi_equi_hier_1.
Also create two private class methods
get_all_equnr - For getting the list of equipment having parent
get_top – For getting the list of equipment and its top equipment
class ZCL_GET_HIER definition
public
final
create public .
public section.
TYPES: BEGIN OF ty_equi,
equnr TYPE equnr,
hequi TYPE equnr,
END OF ty_equi,
BEGIN OF ty_equnr,
equnr TYPE equnr,
END OF ty_equnr,
tty_equi TYPE STANDARD TABLE OF ty_equi,
tty_equnr TYPE STANDARD TABLE OF ty_equnr.
data: lv_data type char1,
lv_equnr type equnr,
lv_hequi type equnr,
lwa_equi TYPE ty_equi,
lt_equi TYPE STANDARD TABLE OF ty_equi.
INTERFACES IF_AMDP_MARKER_HDB.
class-methods: GET_TOP_HIER FOR TABLE FUNCTION zi_equi_hier_1.
protected section.
private section.
class-methods: get_top EXPORTING VALUE(et_equi) TYPE tty_equi,
get_all_equnr EXPORTING VALUE(et_equnr) TYPE tty_equnr.
ENDCLASS.Now implement method get_all_equnr having parent, this method is being called from get_top method.
METHOD get_all_equnr BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY USING equz.
declare lv_mandt "$ABAP.type( MANDT )" := session_context('CLIENT');
et_equnr = select equnr
from equz
where mandt = :lv_mandt
and hequi <> '';
endmethod.Now implement method get_top to get the list of equipment and its top equipment
Call get_all_equnr method to get the list of equipment having parent
Start first loop for each of the equipment got from above method.
Now for each equipment start second loop to gets its top most equipment
Start Second Loop until hequi is empty in EQUZ table
If hequi is not empty then replace its value with equnr to be used in next iteration
Else fill exporting table value and end second loop.
Check if we reached to the last equipment
If yes then end first loop
Else goto next equipment in first loop
METHOD get_top BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY USING equz ZCL_GET_HIER=>GET_ALL_EQUNR.
declare lv_mandt "$ABAP.type( MANDT )" := session_context('CLIENT');
DECLARE lv_dat int;
DECLARE lv_data int;
DECLARE lv_ind int;
DECLARE lv_equi char( 18 );
DECLARE lv_equnr char( 18 );
DECLARE lv_hequi char( 18 );
CALL "ZCL_GET_HIER=>GET_ALL_EQUNR"( et_equnr => lt_equi );
lv_ind = 1;
lv_data = 1;
while:lv_data = 1 DO
lv_dat = 1;
while:lv_dat = 1 DO
if :lv_equi = ''
then
lv_equi = :lt_equi.equnr[lv_ind];
lv_equnr = :lt_equi.equnr[lv_ind];
ELSEIF :lv_equi IS NULL
then
lv_equi = :lt_equi.equnr[lv_ind];
lv_equnr = :lt_equi.equnr[lv_ind];
END IF;
begin
lt_data = select hequi as hequi
from equz
where mandt = :lv_mandt and
equnr = :lv_equi;
lv_hequi = :lt_data.hequi[1];
if :lv_hequi = ''
then
et_equi.equnr[lv_ind] = :lv_equnr;
et_equi.hequi[lv_ind] = :lv_equi;
lv_dat = 0;
ELSE
lv_equi = lv_hequi;
END IF;
end;
end WHILE ;
lv_ind = lv_ind + 1;
lv_equi = :lt_equi.equnr[lv_ind];
if :lv_equi IS NULL
then
lv_data = 0;
else
lv_equi = '';
lv_equnr = '';
end if;
end WHILE ;
ENDMETHOD.Now implement method GET_TOP_HIER this will return data to the table function
First call GET_TOP method to get the list of equipment and top level equipment
Then join the result table with the EQUZ to get the final output
method GET_TOP_HIER BY DATABASE FUNCTION
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY USING equz ZCL_GET_HIER=>GET_TOP.
CALL "ZCL_GET_HIER=>GET_TOP"(et_equi => lt_equi);
RETURN
select eq.mandt as mandt,
eq.equnr as equnr,
eq.hequi as pequi,
teq.hequi as hequi
from equz as eq
LEFT OUTER JOIN :lt_equi as teq
ON teq.equnr = eq.equnr
where eq.mandt = :mandt;
endmethod.Step 3 – Use CDS Table Function in a new CDS View
Create new CDS ZEQUI_HIER_2 and use above created table function
@AbapCatalog.sqlViewName: 'ZEQUI_HIER2'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ZI_EQUI_HIER_1'
define view ZEQUI_HIER_2
as select from zi_equi_hier_1( mandt : $session.client ) as zeq
{
key zeq.equnr as Equipment,
zeq.pequi as Parent_Equipment,
zeq.hequi as Top_Level_Equipment
}
Step 4 – Execute New CDS view and get the desires out
Execute new CDS ZEQUI_HIER_2 and here is the result !!
Conclusion
Hence we can see that by utilizing the features of AMDP class/methods we can write calculations/business logic in the database level making it more towards code-push-down.
As an ABAPer writing code in SQLScript can be bit difficult at first, but with practice it will become as simple as ABAP.
Refer below link for more basic understanding on SQLScript
SAP HANA SQLScript Reference
I hope you find this blog post useful and benefit from it. Feel free to ask any question or provide your feedback in the comment section below.
Never stop learning, because life never stops teaching !!
- SAP Managed Tags:
4 Comments
