Technical Articles
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
- Scenarios where existing ABAP logics has to be converted to AMDP to avail the features of HANA transformation
- 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!