Handling of SELECT-OPTIONS parameters within AMDP
The ABAP Managed Database Procedures (AMDP) framework provides the higher level of integration of the advanced HANA capabilities into ABAP applications. It allows creating and managing SQLScript-based DB procedures from the ABAP platform by using so called AMDP methods.
One difficulty faced by developers when working with AMDPs is the handling of SELECT-OPTIONS parameters (selection tables or range tables). The present blog will exactly tackle that topic.
Simply explained, the handling of SELECT-OPTIONS parameters in the context of AMDPs requires two steps:
- Conversion of the selection tables into an SQL WHERE clause using method CL_SHDB_SELTAB=>COMBINE_SELTABS( )
- Handling of dynamic WHERE clauses within the AMDP method using the function APPLY_FILTER
Step 1: Conversion of SELECT-OPTIONS parameters into an SQL WHERE clause
For those of you who have been generating dynamic WHERE clauses till now using the class CL_LIB_SELTAB: Do no longer use it again and if possible even replace such calls (as explained below) in your existing code!
The new class CL_SHDB_SELTAB – especially its static method COMBINE_SELTABS( )– shall be used for this purpose instead. It provides a comfortable coverage of the conversion functionality for SAP HANA (refer to SAP Note 2124672 – SMP login required). This conversion routine includes checks for SQL injections during the conversion of the selection tables into an SQL WHERE clause. ABAP 7.4 SP08 and higher is required in order to apply the above mentioned SAP Note.
Here is a simple demo report showing how to convert the SELECT-OPTIONS parameters into a WHERE clause:
As shown above, you just have to pass an internal table (defined here using the new value operator VALUE) filled with as many SELECT-OPTIONS parameters as required by your scenario. The name of the relevant field (NAME) and of the data reference to the corresponding SELECT-OPTIONS table (DREF) is required for each entry. In case of relevance, it is recommended to specify the exporting parameter IV_CLIENT_FIELD with ‘CLIENT’ or ‘MANDT’ (depending on the related table field name) to ensure the addition of the client filter to the WHERE clause.
The method returns the dynamic WHERE condition as a string which can then be passed to the AMDP method. Let’s now go to the next step.
Step 2: Handling of dynamic WHERE clauses within the AMDP method
What needs to be done is very simple: The SQLScript statement APPLY_FILTER is used to apply the selection criteria to the relevant dataset which can be a database table/view, a HANA view (except Analytical view) or an intermediate table variable.
Below you can see a code simple showing how to apply the dynamic WHERE clause in both cases; directly on a data source (table or view) [CASE 1] or on an intermediate dataset (table variable) [CASE 2].
The APPLY_FILTER function expects two parameters. The first one is the dataset to which you want to apply the filter and the second one is the generated WHERE clause which is passed as a string argument. Find more information about the APPLY_FILTER function in the SAP HANA SQLScript reference.
That’s it… bye!