Create Dates Table CDS Table Function using SAP HANA AMDP Class
Using SQLScript on SAP HANA database SERIES_GENERATE_DATE series data function can be used to create dates table. To create and use a dates table, ABAP programmers can create a CDS table function with the help of AMDP class methods and consume the SQLScript series_generate_date function in the class method implementation codes. In this SAP HANA CDS tutorial, I want to share how to create a CDS table function and create a dates table on SAP S/4HANA using table function CDS.
To create a dates table on SAP HANA database it is easy and performs best to use series data function SERIES_GENERATE_DATE. HANA database developers can create native HANA database table valued functions that can return a dates table on the fly using this method. But if you have problems or limitations to consume native HANA database development objects, an alternative way is to create CDS table functions. In this HANA tutorial, I want to show two CDS table functions that return dates table that can be consumed in ABAP programs.
First dates table function will returns a fixed number of dates that is defined with an input parameter starting from an other input date parameter.
For example, 10 dates starting from '01.01.2018', etc.
I will name this function as GetNDatesStartingFrom (for Get N Dates Starting From)
Second dates tabls function will be used to return dates between a given range defined by two boundary date parameters.
For example, dates between '01.01.2018' and '31.01.2018', etc.
I will call this second dates table function as GetDatesBetween (for Get Dates Between)
Launch SAP HANA Studio and use SAP HANA Development perspective.
On Project Explorer window connect to target SAP S/4HANA system.
On the ABAP development package in which you will create the CDS table functions, right click on the package and choose New in context menu.
We will create a new Core Data Services Data Definition object. You can filter and select as seen in following screenshot.
Click Next and on the following screen define a name and description for your first CDS table function.
SAP HANA Studio will provide predefined templates. Among the existing templates, choose Define Table Function with Parameters
Click Finish button to start coding for our first CDS table function.
I modified the initially displayed template as follows:
@ClientDependent: false
@EndUserText.label: 'Get N Dates Starting From'
define table function ZGetNDatesStartingFrom
with parameters
starting_date : abap.dats,
number_of_dates : abap.int2
returns {
--client : abap.clnt;
number : abap.int8;
date : abap.dats;
}
implemented by method class_name=>method_name;
Since this dates table is not a client specific data, I set @ClientDependent as false and excluded client column from returned table field list.
As seen in the last code line, the code actually executed by this CDS Table Function is covered within class_name=>method_name which we have not created yet.
So let's now continue with creating our AMDP class code and method to implement CDS table function code.
On package name, right click and choose New > ABAP Class
Type a meaningful brief class name and a descriptive text for the AMDP class created for methods which will be consumed by CDS table functions.
Here is our initial ABAP class code which is still not an AMDP class. You can refer to tutorial create AMDP procedure for details.
CLASS zcl_datestable DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_datestable IMPLEMENTATION.
ENDCLASS.
At this point, we know the name of the AMDP class that we plan to use; zcl_datestable.
And if you already know the class method name, for example GetNDatesStartingFrom, we can modify the last code line of the CDS table function.
For example, I changed below code line from CDS Table Function
implemented by method class_name=>method_name;
to below code line replaced with new class name and method name to use for this CDS table function
implemented by method zcl_datestable=>GetNDatesStartingFrom;
ABAP programmer can also save and activate the CDS table function though the class and the class method have not been created yet.
Let's switch to AMDP class code and create the method definition and its implementation as follows.
If you did not activate the CDS Table Function before this step, you will probably get a similar error on SAP HANA Studio indicating:
'ZGETNDATESSTARTINGFROM' is not a table function.
If you have already activated the CDS table function definition after modifying the class_name=>method_name part, then you can continue with following step.
Here is the AMDP class codes to use.
I copy down all required code including the definition of the AMDP function class, its method definition and implementations including with SQLScript code where SQLScript Data Series function SERIES_GENERATE_DATE is used to populate and return the desired dates table for the ABAP programmer.
CLASS zcl_datestable DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
class-methods GetNDatesStartingFrom for TABLE FUNCTION ZGetNDatesStartingFrom.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_datestable IMPLEMENTATION.
METHOD GetNDatesStartingFrom
BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT.
-- SQLScript Code ->
return
SELECT
element_number as number,
dats_from_date( generated_period_start ) as date
FROM SERIES_GENERATE_DATE(
'INTERVAL 1 DAY',
:starting_date,
add_days(:starting_date, :number_of_dates)
);
-- <- SQLScript Code
ENDMETHOD.
ENDCLASS.
After you save and activate this AMDP Table Function, ABAP programmers are ready to call this AMDP class method or CDS Table Function within an ABAP program.
Create a test ABAP program and copy following code.
data lv_startdate TYPE dats.
data lv_i TYPE i VALUE 10.
lv_startdate = sy-datum.
select *
from ZGetNDatesStartingFrom(
starting_date = @lv_startdate,
number_of_dates = @lv_i
)
INTO TABLE @data(lt_data).
LOOP AT lt_data REFERENCE INTO data(lr_data).
WRITE :/ lr_data->number, lr_data->date.
ENDLOOP.
ABAP developers will see a warning indicating that The database feature "AMDP_TABLE_FUNCTION" is used here (read the long text). on SAP HANA Studio editor.
To resolve this warning, place ##db_feature_mode[amdp_table_function] pragma at the end of the SELECT statement where AMDP Table Function is referenced in FROM clause as follows:
select *
from ZGetNDatesStartingFrom(
starting_date = @lv_startdate,
number_of_dates = @lv_i
)
INTO TABLE @data(lt_data)
##db_feature_mode[amdp_table_function].
Here is the output displayed on screen when the above ABAP program is executed which is listing 10 dates starting from today
ABAP programmers can use dates table function with other SAP tables in an OpenSQL query as follows
select vbeln, kunnr, erdat, date
from ZGetNDatesStartingFrom(
starting_date = @lv_startdate,
number_of_dates = @lv_i
) as dt
INNER JOIN vbak
on dt~date = vbak~erdat
INTO TABLE @data(lt_data).
Please pay attention to the syntax of function table accepting input parameters and INNER JOIN clause with additional SAP table in this sample ABAP OpenSQL query.
ABAP developers can see the outcome of the query. Please note that the date field from CDS dates table function is in dats format just like erdat field of VBAK SAP table.
If you want to use dates table function in CDS views you have created using SAP HANA Studio instad of using in ABAP OpenSQL queries, following CDS view can be an example.
Please note that I've added the table function parameters as parameters of the CDS view (parametric CDS view parameters)
@AbapCatalog.sqlViewName: 'ZNDATESFROM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Dates Table Function in CDS View'
define view ZDatesTableFunctionInCDSView
with parameters
p_startdate : abap.dats,
p_numberofdates : abap.int4
as
select
vbeln,
kunnr,
erdat
from vbak
inner join ZGetNDatesStartingFrom (
starting_date : $parameters.p_startdate,
number_of_dates : $parameters.p_numberofdates
) as dt
on vbak.erdat = dt.date;
Let's now add our second dates table function.
As we have learnt from above steps in this tutorial, first we will create and activate our CDS table function using the AMDP class name and method name.
As the second step, ABAP programmers will add a second method into our AMDP class.
This new method will be used by CDS table function.
Within the AMDP class method we will place the required SAP HANA database SQLScript code.
Let's start. I will not repeat many things and note here. I will just place codes.
Here is the SAP HANA CDS Table Function definition and included code.
@ClientDependent: false
@EndUserText.label: 'Get Dates Between'
define table function ZGetDatesBetween
with parameters
starting_date : abap.dats,
end_date : abap.dats
returns {
-- client : abap.clnt;
number : abap.int8;
date : abap.dats;
}
implemented by method zcl_datestable=>GetDatesBetween;
Now, let's switch to AMDP class code and add a second method named GetDatesBetween
CLASS zcl_datestable DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
class-methods GetNDatesStartingFrom for TABLE FUNCTION ZGetNDatesStartingFrom.
class-methods GetDatesBetween for TABLE FUNCTION ZGetDatesBetween.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_datestable IMPLEMENTATION.
METHOD GetNDatesStartingFrom
BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT.
-- SQLScript Code ->
return
SELECT
-- '060' as client,
element_number as number,
dats_from_date( generated_period_start ) as date
FROM SERIES_GENERATE_DATE(
'INTERVAL 1 DAY',
:starting_date,
add_days(:starting_date, :number_of_dates)
);
-- <- SQLScript Code
ENDMETHOD.
METHOD getdatesbetween
BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT.
-- SQLScript Code ->
return
SELECT
-- '060' as client,
element_number as number,
dats_from_date( generated_period_start ) as date
FROM SERIES_GENERATE_DATE(
'INTERVAL 1 DAY',
:starting_date,
add_days(:end_date, 1)
);
-- <- SQLScript Code
ENDMETHOD.
ENDCLASS.
Let's modify our ABAP program and execute a second SQL Select statement using our CDS Table Function that represents a date table
DATA lv_startdate TYPE dats.
DATA lv_enddate TYPE dats.
DATA lv_i TYPE i VALUE 10.
lv_startdate = sy-datum.
lv_enddate = '20180630'.
* list N date values starting from D
SELECT *
FROM zgetndatesstartingfrom(
starting_date = @lv_startdate,
number_of_dates = @lv_i
)
INTO TABLE @DATA(lt_data)
##db_feature_mode[amdp_table_function].
* list dates between D1 and D2
SELECT *
FROM zgetdatesbetween(
starting_date = @lv_startdate,
end_date = @lv_enddate
)
INTO TABLE @DATA(lt_data2)
##db_feature_mode[amdp_table_function].
LOOP AT lt_data REFERENCE INTO DATA(lr_data).
AT FIRST.
WRITE :/ |List of { lv_i } Dates Starting From { lv_startdate }|.
ENDAT.
WRITE :/ lr_data->number, lr_data->date.
AT LAST.
WRITE :/ .
ENDAT.
ENDLOOP.
LOOP AT lt_data2 REFERENCE INTO DATA(lr_data2).
AT FIRST.
WRITE :/ |Dates Between { lv_startdate } and { lv_enddate }|.
ENDAT.
WRITE :/ lr_data2->number, lr_data2->date.
AT LAST.
WRITE :/ .
ENDAT.
ENDLOOP.
And the execution output of the above ABAP program is seen in following screenshot.
I hope this CDS tutorial will be useful for SAP programmers who want to create dates table function and using in their ABAP program using SQLScript data series functions like series_generate_date
If ABAP programmer wants to use CDS table function within SQL SELECT statement joining other SAP tables, following usage can be a sample case.
In SELECT query, right after dates table function we used a table alias using "as dt" and used this "dt" alias in the "INNER JOIN" clause
select vbeln,kunnr, erdat, date
from ZGetDatesBetween(
starting_date = @lv_startdate,
end_date = @lv_enddate
) as dt
INNER JOIN vbak
on dt~date = vbak~erdat
INTO TABLE @data(lt_data).
Of course, ABAP developers can use this dates table function in CDS views as well.
Following parametric CDS view is an example showing how dates table function can be used as part of the CDS view query.
@AbapCatalog.sqlViewName: 'ZDATESBETWEEN'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Dates Table Function in CDS View'
define view ZDatesTableFunctionInCDSView
with parameters
p_startdate : abap.dats,
p_enddate : abap.dats
as
select
vbeln,
kunnr,
erdat
from vbak
inner join Z_SCF_DatesBetween (
starting_date : $parameters.p_startdate,
end_date : $parameters.p_enddate
) as dt
on vbak.erdat = dt.date;
