Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
RichHeilman
Developer Advocate
Developer Advocate

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.

CREATE PROCEDURE get_products_by_filter(

            IN im_filter_string VARCHAR(5000),

            out ex_products “SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::products" )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

ex_products =

APPLY_FILTER("SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::products",

                   :im_filter_string) ;

END;

35 Comments