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



Comments

Post a Comment

Popular posts from this blog

Q:  What is a nested “if” statement? A:  A nested “if” statement is the true condition in a series of conditions in computer programming.  It is used when multiple responses are possible and the outcome for each response is different. As Per My Knowledge Answers is: A:  IF  with in the  IF  is called “ Nested IF “. Syntax  :  if(condition,doiftrue,doiffalse) Nested If Syntax  :   if(condition,doiftrue, if(condition,doiftrue,doiffalse)) IF  with in the  IF . In the example shown, we are using nested IF functions to assign grades based on a score. The logic for assigning a grade goes like this: Score Grade 0-63 F 64-72 D 73-84 C 85-94 B 95-100 A To build up a nested IF formula that reflects this logic, we can start by testing to see if the score is below 64. If TRUE, we retur...
Read more

How To Use Decision Table In Calculation View In SAP HANA

Image
A decision table is an excellent tool to use in both testing and requirements management. Essentially it is a structured exercise to formulate requirements when dealing with complex business rules. Decision tables are used to model complicated logic. They can make it easy to see that all possible combinations of conditions have been considered and when conditions are missed, it is easy to see this. First create the EMPLOYEE table with following data Create New Decision Table and select EMPLOYEE table in the Data Foundation Make SALARY column as Condition and create new parameter HIKE make it as Actions. Click on Decision Table and give the conditions like below. Now Create Calculation view and select EMPLOYEE Table and EMPLOYEE_SALARY Decision Table In EMPLOYEE table select all columns, from EMPLOYEE_SALARY Decision Table select all columns. In Join join the EMPLOYEE Table  ID with EMPLOYEE_SALARY Decision Table ID and select the...
Read more