Skip to Content

APPLY_FILTER : Putting Dynamic source

Hi Friends,

According to below link:

Using Dynamic Filters in SAP HANA by Rich Heilman, APPLY_FILTER statement does not use dynamic SQL and no association with EXEC statement, so there should be no related performance issues when using APPLY_FILTER.

I have a procedure which has multiple Dynamic SQL with Exec Statement. So, I need to optimize them. However, my source which happens to be a calculation view vary with different condition, Hence, my source for APPLY_FILTER will also by dynamic. The POC is shown below where P_SOURCE_CV will be an input from other procedure based on some parameters. When I try to activate this procedure, I get an error - SAP DBTech JDBC: [1310]: scalar type is not allowed: P_SOURCE_CV: line 9 col 1 (at pos 183)

PROCEDURE "TEST_APPLYFILTER" ( 
 IN P_SOURCE_CV VARCHAR(400)
) 
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER 
AS
BEGIN
DECLARE V_FILTER VARCHAR(1000) DEFAULT 'COMPANY_CD = ''1001''';
ex_products = APPLY_FILTER(:P_SOURCE_CV, :V_FILTER ) ;
SELECT * FROM :ex_products;
END;

Please tell me how to achieve above scenario with APPLY_FILTER.

Thanks,

Gaurav

HANA 2.0 SP3

Add a comment
10|10000 characters needed characters exceeded

  • Why do you need APPLY_FILTER when your V_FILTER is fixed value (COMPANY_CD = 1001) ? Can't you simply use WHERE COMPANY_CD = 1001 ?

  • Hi Konrad,

    I have used V_FILTER as an example. In realistic world, COMPANY_CD and many other parameters such as FISCAL_PERIOD would come as an input into the procedure and then we will have to construct the string.

    'COMPANY_CD = ''1001'''is just an example
    Realistically it will be:
    V_FILTER ='COMPANY_CD = '|| P_COMPANY_CD ||'AND FISCAL_PERIOD = '||P_FISCAL_PRD;where P_COMPANY_CD and P_FISCAL_PRD are procedureinputparameters

    Since my question was more on the source side thats why I did not put much emphasis on filter. How do you pass a dynamic source P_SOURCE_CV inside APPLY_FILTER is my issue.

    Thanks,

    Gaurav

Related questions

0 Answers

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.