Solution for Defining and Using Scalar Functions from AMDP

[samson_moses]

Hi All,

I have a requirement, where I have to do some Non-DB specific operations, on my selected data in an AMDP Method. This logic is a pretty lengthy and I don't want to use CASE..ENDCASE based evaluations, in my SELECT statement as the Procedure logic would be very cumbersome. Ideally this should qualify for a scalar UDF.

Requirement Example construct :

METHOD My_AMDB_Procedure ......( Additions for Defining as Procedures).

lt_result := SELECT Data_1, Data_2, Derive_Data3_Function( Data_1, Data_2) AS Data_3

FROM my_table_data ;

ENDMETHOD.

Additionally, I want to avoid the option of creating the function in HANA DB but have to manage life cycle management from ABAP.

Based on my exploration, I realised that, from AMDP, only Table functions can be defined and also these cannot be called in my field projection section for data selection. So the above solution is not feasible from AMDP.

Alternatively I tried defining my function as a table function like below :

METHOD Derive_Data3_Function. ( Inputs for Data1, Data2, Returning table with 1 field called Data3 )

DECLARE l_data3 TYPE nvarchar(10).

<........My SQL Script code having Imperative logic and setting value of l_data3......>

RETURN SELECT l_data3 AS DATA3 FROM dummy ;

ENDMETHOD.

And also tried deriving my required field as below :

METHOD My_AMDB_Procedure ......( Additions for Defining as Procedures).

lt_result := SELECT Data_1, Data_2,

( SELECT Data3 FROM "MY_AMDP_CLASS=>DERIVE_DATA3_FUNCTION"( Data_1, Data_2) ) AS Data_3

FROM my_table_data ;

ENDMETHOD.

Now, I get the error that "SQLSCRIPT message: feature not supported: field or table alias is not allowed as an input of table function".

Note : I can pass hard-code values instead of selected fields and it shows no error.

Kindly suggest what should be my approach in this case , as I want to push my ABAP level imperative logic into DB.

Our System version is as below :

ABAP : 7.50 Level 11 / HDB 2.00.036.00

Regards,

Samson.

[pfefferf · Accepted solution]

As you already described until not it is not possible to define a scalar function via AMDP mechanisms. The usage of a table function in the projection list of a query with the expection that it is called for each projection result automatically referring to values of the lines is not possible to (as you see with the reported error message). Now of course it would be possible to create a loop/cursor construct to get first the data for the table and then do for each line a call to the table function, but that is not the best option from a performance point of view. SAP provides for that the MAP_MERGE operator which allows to do that in a more efficient way. The code looks then somehow like following:

lt_map_merge_input = select Data_1, Data_2 from my_table_data;
lt_result = map_merge(:lt_map_merge_input, "MY_AMDP_CLASS=>DERIVE_DATA3_FUNCTION" (:lt_map_merge_input.Data_1, :lt_map_merge_input.Data_2);
...

Of course the used table function (derive_data3_function) needs to return the complete result you expect, or you provide the data which is necessary to be able to join it with the selected data from your my_table_data table.

[samson_moses · Accepted solution]

Thanks a lot Florian, for your response,

I changed my logic to what you proposed and now using MAP_MERGE and table functions, at-least I am able to segregate my lengthy, cumbersome non-DB operational logic outside of my SELECT.

I would be now checking the performance aspect of the new approach .

Hope SAP does come up with allowing scalar AMDP functions definitions.

Thanks a ton again.

Regards,

Samson