Creation of dynamic filters in SAP HANA Schema

With SAP HANA 1.0 SPS6(Rev 60), we can now leverage the concept of dynamic filters.   There have been several requests for this type of functionality, since SAP does not recommend the use of dynamic SQL(EXEC statement) when developing SQLScript procedures.  We now have a new statement in SQLScript called APPLY_FILTER.  This statement accepts two parameters.  The first parameter is the dataset in which you want to apply the filter.  This dataset can be a database table, database view, HANA attribute or calculation view, or even an intermediate table variable.  The second parameter is of course the filter condition itself. This would be very similar syntax that you would use in the WHERE clause of a SELECT statement.   In the following example, I have a SQLScript procedure which simply reads data from the “Products” table and applies a filter which is passed as an input parameter to the procedure.  The result set then shows the filtered dataset.

Creation of dynamic filters in SAP HANA

Make one product table with following columns

Insert your table with following details

Create one table type in your schema, which doesn’t store physical data of the table but only acts as a virtual table for your products table with below syntax

create type “KRISHNA_REDDY”.”tt_emp1″ as table

(EMPLOYEE_NAME varchar(20) CS_STRING,

EMPLOYEE_AGE INTeger ,

EMPLOYEE_DESIGNATION VARCHAR(20) CS_STRING,

EMPLOYEE_SALARY INTeger ,

EMPLOYEE_CURRENT_LOCATION VARCHAR(20) CS_STRING

)

Now create one stored procedure with following syntax

CREATE PROCEDURE “KRISHNA_REDDY”.”PROCEDURE_DYNAMIC_FILTER”(

IN im_filter_string VARCHAR(5000),

OUT output_table “KRISHNA_REDDY”.”tt_emp1″ )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

AS

/*********BEGIN PROCEDURE SCRIPT ************/

BEGIN

output_table = APPLY_FILTER(“KRISHNA_REDDY”.”EMPLOYEE_LOCATION_RESTRICTION”, :im_filter_string) ;

END;

Call this procedure with syntax

call “KRISHNA_REDDY”.”PROCEDURE_DYNAMIC_FILTER”(im_filter_string => ‘”EMPLOYEE_NAME” = ”PRAVEEN”’,output_table => ?);

Results