SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SQLScript
+
-
Tables in SAP HANA
+
-
Procedure
+
-
Examples of Procedure
Dynamic Filter in HANA


In this article we will show an example on - How to implement and call dynamic filtering in 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 in 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.

----REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
---- Create Product table
create column table "<SCHEMA_NAME>"."PRODUCT"(
      "PRODUCT_ID" VARCHAR(10),
      "PRODUCT_NAME" VARCHAR (100),
    "CATEGORY" VARCHAR(100),
      primary key ("PRODUCT_ID")
);

insert into "<SCHEMA_NAME>"."PRODUCT" values('P1','Shirts', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P2','Jackets', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P3','Trousers', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P4','Coats', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P5','Purse', 'Accessories');


Create table type:


CREATE TYPE <SCHEMA_NAME>.TT_PRODUCT AS TABLE(
      "PRODUCT_ID" VARCHAR(10),
      "PRODUCT_NAME" VARCHAR (100),
      "CATEGORY" VARCHAR (100)
);

To know more about table type, read SAP HANA Table Type.

Create procedure:

Copy and paste the below script to create the procedure.

----REPLACE < SCHEMA_NAME > WITH YOUR SCHEMA NAME
CREATE PROCEDURE <SCHEMA_NAME>."PROCEDURE_DYNAMIC_FILTER"(
        IN im_filter_string VARCHAR(5000),
        OUT output_table <SCHEMA_NAME>."TT_PRODUCT" )
    LANGUAGE SQLSCRIPT 
    SQL SECURITY INVOKER 
    AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
    output_table = APPLY_FILTER("<SCHEMA_NAME>"."PRODUCT", :im_filter_string) ;
END;

Call procedure:

Call the procedure using below statement.

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

SAP HANA Procedure Example

Continue reading:
Check the next example on Exception Handling in HANA




Support us by sharing this article.



Explore More
Close X
Close X

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy