SAP HANA: Dynamic Filter Using Procedure

Dynamic Filter in HANA

In this article, we will show an example on - How to implement and call dynamic filtering in a procedure.

Introduction:

·         SAP does not recommend the use of dynamic SQL (EXEC statement) when developing SQLScript procedures.

·         For dynamic SQL, 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 the filter condition itself. This would be very similar syntax that you would use in the WHERE clause of a SELECT statement. 

In this example, we are going to use table PRODUCT and pass the filter condition dynamically while calling the procedure. The output will be sent to output using a table type.

Create Table:

Copy and paste the below script in SQL editor and execute.

Note: If you already have created the PRODUCT table in previous example, then skip this step.

To Create a Table:

create column table "KABIL_PRACTICE"."PRODUCT_DF"(

      "PRODUCT_ID" VARCHAR(10),

      "PRODUCT_NAME" VARCHAR (100),

    "CATEGORY" VARCHAR(100),

      primary key ("PRODUCT_ID")

);

To Insert a records into the Table:

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P1','Shirts', 'Clothes');

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P2','Jackets', 'Clothes');

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P3','Trousers', 'Clothes');

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P4','Coats', 'Clothes');

insert into "KABIL_PRACTICE"."PRODUCT_DF" values('P5','Purse', 'Accessories');

To Create a Table Type:

CREATE TYPE "KABIL_PRACTICE"."TT_PRODUCT_DF" AS TABLE(

      "PRODUCT_ID" VARCHAR(10),

      "PRODUCT_NAME" VARCHAR (100),

      "CATEGORY" VARCHAR (100)

);

To Create a Procedure:

CREATE PROCEDURE "KABIL_PRACTICE"."PROCEDURE_DYNAMIC_FILTER"(

        IN im_filter_string VARCHAR(5000),

        OUT output_table "KABIL_PRACTICE"."TT_PRODUCT_DF" )

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    AS

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

BEGIN

    output_table = APPLY_FILTER("KABIL_PRACTICE"."PRODUCT_DF", :im_filter_string) ;

END;

To Call a Procedure:

CALL "KABIL_PRACTICE"."PROCEDURE_DYNAMIC_FILTER"(im_filter_string => '"CATEGORY" = ''Clothes''', output_table => ?);

Result:

Thank you...

Please Share Your Comments...