AMDP code to implement predefined SQL functions in BW transformation

This blog post explains about how to use SQL predefined functions in AMDP code

Business Requirement

1. Scenarios where existing ABAP logics has to be converted to AMDP to avail the features of HANA transformation
2. Use the predefined functions available in sql and get rid of custom logics

Example

In this blog post, a simple example is considered to understand how to implement the predefined functions in BW transformation

Calweek is calculated from the calday, but if the calweek has to be calculated in a customized manner, that is calweek is populated based on the start of the week(Monday-Sunday) , if this has to be customized and the calweek should be populated based on the start of the week(Sunday – Saturday), predefined functions has to be used and the logic has to be achieved. Let’s look into the normal logic to achieve the requirement

Calday – June 09 2019 ;Calweek(Mon-Sun) – 23.2019

Customized Calweek (Sun-Sat) for June 09.2019 is 24.2019

Pseudocode: 

Get the number of the day from the calday

If it is 7, add 1 to the calday and calculate the week.

This scenario is performed in most of the requirements using ABAP FM’s or Formula’s. Here we will look into the equivalent AMDP code for this.Same logic was implemented using formula’s but the trfn was not changed to HANA trfn, so AMDP has to be implemented to make the trfn as HANA trfn

SQL date functions:

weekday – gives the number of the day(0 to 6)

add_days – add number of days to the date

isoweek – returns the week of the date in the specified format(YYYY-WNN)

Steps:

Create a infoobject of length 8

Select AMDP script for the new Infoobject in the TRFN, source Calday

By default, AMDP script creates a class and the method “Procedure”(More about AMDP generated class and method in another blog)

inTab and outTab are the import and export tables, inTab  has all the source fields, here it is calday. outTab is in the structure of target field.

AMDP scrips has to be implemented using ABAP perspective in Hana Studio

AMDP Code:

Result

This is a basic example on how to use predefined functions in AMDP, there are lots of date, string ,numerical functions to perform all the requirement.

Thanks for reading and happy learning!