Skip to Content
Technical Articles
Author's profile photo Abhishek Hazra

Consume Dynamic SQL in AMDP within BW/4HANA TRFN : generating table name dynamically

Context

This blog post has a post reference to my previous blog post, where I had explained a scenario which involved aloading scenario where data target was an aDSO having several source aDSOs with similar structure. In the runtime we read the active table of the respective source aDSO using a common transformation between two infosources consisting of AMDP start routine. In the code used in my previous blog post had the direct reference to the source aDSOs (in ‘USING’ clause & then left outer joins with :inTab). However, I went on trying to make this derivation dynamic. Imagine we end up having more source aDSOs for business need & everytime we have to modify the existing AMDP to add & hardcode the new aDSO’s active table reference. In order to do that, we sought help of dynamic SQL where we derive the active table name based on the AMDP parameter :i_req_src_name (the technical name of source aDSO of the TRFN).

Pre-observations

It’s important to state before I start explaining the actual solution I followed, that I have made this illustration based on HANA 2.0 & I tried to build a procedure which upon passing the aDSO’s name as input parameter would prepare the active table name of the same & use that in the from clause of the select & derive the maximum billing date available from that table into output parameter to be used later when being called from the encapsulating AMDP start routine. However it did not work for the following reasons – firstly, the created procedure would be residing in a schema which is outside SAP DB owned DDIC dictionary object schema & thus it would be unknown to the AMDP. Also, the prerequisite to use the procedure is that the procedure should be within AMDP namespace, meaning the name of the procedure should start with ‘/1BCAMDP/’. But due to obvious authorization reasons, the stored procedure could not be created within the SAP DB owned schema, so the stored procedure continued to show errors while being called from the AMDP start rouine. Secondly I stumbled upon creating the procedure or a function within the same AMDP start routine since the DDL statements like CREATE/DROP are only allowed in a read only AMDP in case of tables & not for proceudres or functions.

Case Study

I will explain the requirement as introduction of the case study. Let’s take the same example from my previous blog post : I have 3 aDSOs which load data to a single aDSO target. The 3 source aDSOs have billing dates respectively for 3 different countries, for a particular combination of store & article in the delta data we need to find the maximum available billing date in the active tables of the aDSOs & stamp that into the target aDSO as shown in the example in the blog post. Now imagine if we add a fourth aDSO as source, we need to change the AMDP to include the name of the active table in the join condition, similarly everytime we get a new country as source aDSO. To avoid modification in the code everytime, I tried to make the code dynamic, so that it can derive the max(BILL_DATE) from the active table of the source of the load without being hardcoded. As explained in earlier blog post, the technical name of the source aDSO is already available as parameter :i_req_src_name in global area of the AMDP. So, basically I needed a variable to derive the table name by concatenating “/BIC/’A’ & ‘2’ in beginning & at the end of the parameter value :i_req_src_name.

Now as I stated the problems regarding creating a procedure or function residing in a schema outside the DB owner schema, the solution was to write the code in another AMDP method. This way it is secured to be placed within the same DB owned schema as DDIC definitions. Below is the AMDP method for preparing the active table name from the techical name of the aDSOs & deriving the max(BILL_DATE) with help of EXECUTE IMMEDIATE statement.

** Declaration of input & outptut parameters :

TYPES: BEGIN OF LS_INTAB,
         ADSO TYPE c LENGTH 9,
         STORE TYPE c LENGTH 4,
         ARTICLE TYPE c LENGTH 18,
         END OF LS_INTAB.
  TYPES: LT_INTAB TYPE TABLE OF LS_INTAB.


  TYPES: BEGIN OF LS_OUTTAB,
         STORE TYPE c LENGTH 4,
         ARTICLE TYPE c LENGTH 18,
         BILL_DATE TYPE c LENGTH 8,
         END OF LS_OUTTAB.
  TYPES: LT_OUTTAB TYPE TABLE OF LS_OUTTAB.

  CLASS-METHODS MAX_BILL_DATE
      IMPORTING
        VALUE(INTAB) TYPE LT_INTAB
      EXPORTING
        VALUE(OUTTAB) TYPE LT_OUTTAB.


** Implementation of the AMDP method :

CLASS ZCL_MAX_BILL_DATE IMPLEMENTATION.

  METHOD MAX_BILL_DATE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.

    DECLARE LV_ADSO NVARCHAR(9);
    SELECT DISTINCT ADSO INTO LV_ADSO FROM :INTAB;

    EXECUTE IMMEDIATE
     'SELECT STORE, ARTICLE, MAX(BILL_DATE) as "BILL_DATE" FROM "<SCHEMA_NAME>"."/BIC/A' || :LV_ADSO  || '2" GROUP BY STORE, ARTICLE' into OUTTAB;

  ENDMETHOD.

ENDCLASS.

In the input parameter of the reusable AMDP we have to pass the store, article information along with the aDSO’s technical name retrieved from :i_req_src_name. This is storded in a temporary variable lv_adso to form the active table name using the concatenation function with help of dynamic sql statement EXECUTE IMMEDIATE.

Now the passing of inpur parameter table & retriving the output parameter table happens from the caller AMDP in BW/4 TRFN. That part works like below :

 

*** Prepare placeholder local table to store output from called AMDP
DECLARE tt_outp TABLE ( "STORE" NVARCHAR(4),
       "ARTICLE" NVARCHAR(18),
       "BILL_DATE" NVARCHAR(8) );

*** Prepare placeholder local table to store input for called AMDP
tt_inp = select distinct :i_req_src_name as "ADSO", STORE, ARTICLE from :intab;


*** Call AMDP to derive last billing date from source ADSO's active table  

"ZCL_MAX_BILL_DATE=>MAX_BILL_DATE"( INTAB=>:tt_inp, OUTTAB=> :tt_outp);

outTab = select 
to.BILL_DATE as BILL_DATE,
ti.STORE,
ti.ARTICLE,
ti.RECORD,
ti.SQL__PROCEDURE__SOURCE__RECORD

from :intab as ti INNER JOIN :tt_outp as to
ON ti.STORE = to.STORE AND
   ti.ARTICLE = to.ARTICLE;

errorTab = select '' as ERROR_TEXT, '' AS SQL__PROCEDURE__SOURCE__RECORD from DUMMY where DUMMY <> 'X';

ENDMETHOD.
ENDCLASS.

 

This way it is totally independent of how many source aDSOs are used to load into the target aDSO in future, it is capable of preparing the active table name by itself & derive the maximum billing date for an article-store combination from the source aDSO’s active table of the respective load requests.

 

Note 

This illustratation is meant to explain the usage of a reusable script within an AMDP which is drafted in BW/4 transformation. There can be other ways to eke out the same without using the dynamic SQL.
Moreover, usage of dynamic SQL in BW/4 TRFN related AMDP routines ofcourse has some downsides & one needs to decide based on proper investigation & comparison between the flexibility & the downsides of using dynamic SQLs. Few of the main drawbacks are :
1. The possibility to look through the where used list of the source aDSO tables are lost in HANA, it’ll  no longer be shown in OBJECT_DEPENDENCIES view since the table names are prepared dynamically.

2. The scope for optimization is little or none in case of complicated scenarios leading to performance penalty.

There could be more such reasons & thus usages of dynamic SQL in AMDPs are rather generally discouraged. But again, it’s the developer or solution maker’s decision based on the comparison between the drawbacks & the flexibility gained.

Hope this blog post will help someone seeking for solution similar to one in context. Also if the readers have some experience/comments/feedbacks to share regarding the same topic, you’re more than welcome 🙂

For Q&As on similar topics, please use the following Q&A section.

 

Best Regards,
Abhi

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Shesh-Dhar Maurya
      Shesh-Dhar Maurya

      Looks to be a good way. Last days i was thinking on how to solve similar issue which i am facing.

      In my case, I have 4 table where data is stored on yearly basis and while running my logic i have to identify the right table based on the year and then read data from that table.

      These tables are on rolling concept means:

      for Example: Table1 stores data for 2018, Table2 stores data for 2019, Table3 stores data for 2020, Table4 stores data for 2021

      At the beginning of year 2022 the tables will change like this: Table1 (delete data of 2018) stores data for 2022, Table2 stores data for 2019, Table3 stores data for 2020, Table4 stores data for 2021

      For this to work i have also a mapping table in the system which is then manually maintained at the year end.

      In ABAP this could be performed easily but in hana script its not allowing to choose table dynamically.

      I am going to try your way. So just to be clear, the steps were

      1. Create a class

      2. Write AMDP method in that class

      3. declare / pass input and output parameter - is this the part of AMDP method in Transformation?

      Thanks.

      Author's profile photo Abhishek Hazra
      Abhishek Hazra
      Blog Post Author

      I am glad that you could use the similar approach.

      You are correct with your apprehensions as you have mentioned, basically you can hold point#1 & #2 together in the implementation part in ABAP perspective. And the input & output parameters (INTAB & OUTTAB for the AMDP class/method) should be declared in the consumed("ZCL_MAX_BILL_DATE=>MAX_BILL_DATE") AMDP.

      The consumer(TRFN-AMDP) AMDP should have placeholders similar to the input parameter of the consumed AMDP (to pass the value to input parameter) & one for holding the output paramater from the consumed AMDP. In this illustration, I have passed temporary table :tt_inp to the input parameter INTAB of the consumed AMDP & then held the data returned by output parameter OUTTAB into another temporary table :tt_outp. Notice that I did not have to declare the :tt_inp table explicitly since it is populated with the select statement structure.

      Best Regards,
      Abhi

       

      Author's profile photo Shesh-Dhar Maurya
      Shesh-Dhar Maurya

      Hi Abhi,

      it seems the approach mentioned above does not work in my case. It could be related to HANA version 1.0.

      While writing the method similar to yours-> ("ZCL_MAX_BILL_DATE=>MAX_BILL_DATE"),

      At line EXECUTE IMMEDIATE it says "SQLSCRIPT:feature not supported:Dynamic SQL is not supported in the READ ONLY procedure/function".

      Do you recommend some other approach?

      Best regards,

      Shesh

       

       

      Author's profile photo Abhishek Hazra
      Abhishek Hazra
      Blog Post Author

      Hi Shesh,

      In the AMDP method can you try again with removing the option for it to be a READ-ONLY? I mean the below part in the beginning of method implementation ?

      OPTIONS READ-ONLY.

      Even though I can see from SAP's help portal that EXECUTE IMMEDIATE is supported from HANA 1.0 SP12 (please refer to : EXECUTE IMMEDIATE), but this seems to be a limitation compared to HANA 2.0.

      However, if that still does not work (I somehow feel that you might get an error while calling it from the TRFN), I can only think of going the conventional ABAP way instead of AMDP.

      Best Regards,
      Abhi