Skip to Content
0

apply_filter with multiple input parameter for fields

Feb 22, 2017 at 12:11 PM

138

avatar image

Hi,

I have a graphical calculation with 13 input parameters and using the same in scripted calculation view and needs to filter data using apply_filter.

Below syntax I have used and it works fine with one input parameter, but i want to filter for multiple like 2 or 3.

How can it be achieved?

var_out = APPLY_FILTER(:var_out1,:IPAUFNR2); --> Works fine

But i want ,

var_out = APPLY_FILTER(:var_out1,:IPAUFNR2,:IPMATNR2); --> Giving error

How to pass multiple input parameters in apply_filter?

Br

sumeet

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Florian Pfeffer
Feb 22, 2017 at 12:30 PM
0

The APPLY_FILTER function only has two parameters. The first parameter is the table to the filtered and the second one is the filter string (a valid where clause to say it in easy words). Your second example does not work, cause you are trying to pass several filter string parameters. You have to combine all the filter strings into one in a valid way.

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Florian,

How can i combine this IPMATNR and IPAUFNR filter paaremeter? Can you please help me with syntax? Or can you please help me with sample code for multiple parameters?

br

sd

0

Please describe the types of your parameters and and example what values you transfer in that parameters. I think it would be also better to have just one parameter for the filter which value is prepare outside of that procedure.

0

Hi Florian,

Here you go. Kindly note that i have AUFNR , MATNR CHARG and WERKS as multiple value parameters and rest parameters are date and time ranges with single values. All I need to pass in below calculation view as a filter parameter and to get the desired O/p in scripted calculation view.

(PLACEHOLDER."$$IPAUFNR$$" => :IPAUFNR2)--> This syntax works in procedure but somehow not working here and giving error and when i google it , i got apply_filter but I am not able to utilize it for multiple filters. kindly help me on the same.

IPAUFNR = '000000400098'

IPMATNR = '000000000000000198'

/********* Begin Procedure Script ************/

BEGIN

DECLARE IPAUFNR2 NVARCHAR(100);

DECLARE IPARBPL2 NVARCHAR(100);

DECLARE IPMATNR2 NVARCHAR(100);

DECLARE IPCHARG2 NVARCHAR(100);

--IPAUFNR2 := ''''||:IPAUFNR||'''';

--IPAUFNR2 := '''||:IPAUFNR||''';

IPAUFNR2 = 'AUFNR in ('||:IPAUFNR||')';

IPMATNR2 = 'MATNR in ('||:IPMATNR||')';

--IPARBPL2 = 'ARBPL in ('||:IPARBPL||')';

--IPMATNR2 = 'MATNR in ('||:IPMATNR||')';

--IPCHARG2 = 'CHARG in ('||:IPCHARG||')';

--IPARBPL2 := ''''||:IPARBPL||'''';

--IPMATNR2 := ''''||:IPMATNR||'''';

--IPCHARG2 := ''''||:IPCHARG||'''';

var_out1 =

SELECTdistinct

-- "COORD_TYPE",

--"WORKCENTER",

"AUFNR",

"GSTRI",

"GSUZI",

"GLTRI",

"GEUZI",

"FTRMI",

"GAMNG",

"GMEIN",

"IGMNG",

"MATNR"

--"WERKS",

--"BWART",

--"CHARG"

FROM"_SYS_BIC"."kgm.dtp.blt/CA_BLT_BWD_POORDER_TEMP";

--from"Z_SLT_REP_BW"."AFKO";

--(PLACEHOLDER."$$IPAUFNR$$" => :IPAUFNR2);

-- ('PLACEHOLDER' = ('$$IPAUFNR$$', '''*'''));

--('PLACEHOLDER' = ('$$IPAUFNR$$', '''000040000420'''));

var_out = APPLY_FILTER(:var_out1,:IPAUFNR2);

--var_out = APPLY_FILTER(:var_out1,:IPMATNR2);

0
sumeet durgia Feb 23, 2017 at 07:36 AM
0

Hi Florian,

Here you go. Kindly note that i have AUFNR , MATNR CHARG and WERKS as multiple value parameters and rest parameters are date and time ranges with single values. All I need to pass in below calculation view as a filter parameter and to get the desired O/p in scripted calculation view.

(PLACEHOLDER."$$IPAUFNR$$" => :IPAUFNR2)--> This syntax works in procedure but somehow not working here and giving error and when i google it , i got apply_filter but I am not able to utilize it for multiple filters. kindly help me on the same.

IPAUFNR = '000000400098'

IPMATNR = '000000000000000198'

/********* Begin Procedure Script ************/

BEGIN

DECLARE IPAUFNR2 NVARCHAR(100);

DECLARE IPARBPL2 NVARCHAR(100);

DECLARE IPMATNR2 NVARCHAR(100);

DECLARE IPCHARG2 NVARCHAR(100);

--IPAUFNR2 := ''''||:IPAUFNR||'''';

--IPAUFNR2 := '''||:IPAUFNR||''';

IPAUFNR2 = 'AUFNR in ('||:IPAUFNR||')';

IPMATNR2 = 'MATNR in ('||:IPMATNR||')';

--IPARBPL2 = 'ARBPL in ('||:IPARBPL||')';

--IPMATNR2 = 'MATNR in ('||:IPMATNR||')';

--IPCHARG2 = 'CHARG in ('||:IPCHARG||')';

--IPARBPL2 := ''''||:IPARBPL||'''';

--IPMATNR2 := ''''||:IPMATNR||'''';

--IPCHARG2 := ''''||:IPCHARG||'''';

var_out1 =

SELECTdistinct

-- "COORD_TYPE",

--"WORKCENTER",

"AUFNR",

"GSTRI",

"GSUZI",

"GLTRI",

"GEUZI",

"FTRMI",

"GAMNG",

"GMEIN",

"IGMNG",

"MATNR"

--"WERKS",

--"BWART",

--"CHARG"

FROM"_SYS_BIC"."kgm.dtp.blt/CA_BLT_BWD_POORDER_TEMP";

--from"Z_SLT_REP_BW"."AFKO";

--(PLACEHOLDER."$$IPAUFNR$$" => :IPAUFNR2);

-- ('PLACEHOLDER' = ('$$IPAUFNR$$', '''*'''));

--('PLACEHOLDER' = ('$$IPAUFNR$$', '''000040000420'''));

var_out = APPLY_FILTER(:var_out1,:IPAUFNR2);

--var_out = APPLY_FILTER(:var_out1,:IPMATNR2);

Show 2 Share
10 |10000 characters needed characters left characters exceeded

I'm a little bit confused about your descriptions.

What is the situation now?
  • You have a calculation view with parameters which accepts multiple values?
  • You wanna do a query on that calculation view transferring parameters to the parameters of the calculation view?
  • The usage of APPLY_FILTER is only a "workaround" for you, cause you are not able at the moment to transfer the parameter values to the parametes of the calc. view?

In case my assumptions are correct:

  • Where do you do the query on the calc. view? In a procedure, table function, scripted calc. view ...?
  • Show the type of the parameters of the calc. view.
  • Show the type of the parameters you are trying to transfer to the parametes of the scripted calc. views.
0

Hi Florian,

Below are you answers:

  • You have a calculation view with parameters which accepts multiple values? --> Yes
  • You wanna do a query on that calculation view transferring parameters to the parameters of the calculation view? Yes
  • The usage of APPLY_FILTER is only a "workaround" for you, cause you are not able at the moment to transfer the parameter values to the parametes of the calc. view? able to pass with single filter but wants to pass multiple filters like below example.

In case my assumptions are correct:

  • Where do you do the query on the calc. view? In a procedure, table function, scripted calc. view ...? Scripted Calculation view
  • Show the type of the parameters of the calc. view AUFNR type nvarchar(11) , MATNR type nvarchar(18).
  • Show the type of the parameters you are trying to transfer to the parametes of the scripted calc. views. IPAUFNR type nvarchar(11) , IPMATNR type nvarchar(18)

This is my scripted calculation view and i want to select values from 'CA_BLT_BWD_POORDER_TEMP'(this is a graphical calculation view) .

Below input parameters I am using of type.

a.) IPAUFNR type nvarchar(11).

b.) IPMATNR type nvarchar(18).

I just want to know how to concatenate both filters and pass it to apply_filter something like below.

var_out = APPLY_FILTER(:var_out1,:IPAUFNR2, :IPMATNR); --> But this is not working

br

sd


0
Florian Pfeffer
Mar 03, 2017 at 12:26 PM
0

As already said you cannot pass more than two arguments to the apply_filter function. You can only pass the table to be filtered and the filter string.

You have to call the apply_filter function either two times or concatenate the filter criteria into one string like e.g.

declare lv_filter nvarchar(255);

lv_filter = '"AUFNR" = ''' || :IP_AUFNR2 || ''' AND "MATNR" = ''' || :IPMANTNR || '''';
var_out = APPLY_FILTER(:var_out1, :lv_filter);

But I still do not understand why you do not just pass the parameters to the calculation view as the calculation view has that parameters according to your description.

Regards,
Florian

Share
10 |10000 characters needed characters left characters exceeded