APPLY_FILTER : Putting Dynamic source

[kgaurav2k14]

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

[KonradZaleski]

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 ?

[kgaurav2k14]

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