SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




Install SAP Free


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.

create new ABAP repository object

We will create a new Core Data Services Data Definition object. You can filter and select as seen in following screenshot.

Core Data Services

Click Next and on the following screen define a name and description for your first CDS table function.

SAP HANA CDS table function definition

SAP HANA Studio will provide predefined templates. Among the existing templates, choose Define Table Function with Parameters

SAP HANA CDS Table Function with Parameters template

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

create AMDP class using SAP HANA Studio

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.

SAP HANA AMDP class definition

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.

AMDP Table Function ABAP and SQLScript codes in SAP HANA Studio

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

OpenSQL Select statement using AMDP Dates Table Function

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;

SAP HANA CDS Dates Table function

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.

AMDP Table Function codes for CDS Dates Table

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.

CDS dates table functions in sample ABAP program

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






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems