Pass multiple values to procedure

[Former Member]

Hi Experts,

I am calling a Calculation View inside a procedure.

My View has a mandatory input parameter and mandatory variable.

I am calling procedure as

BEGIN

SELECT SUM("SALARY"), "EMPNAME", "EMPID","COUNTRY",'REGION","DEPARTMENT"

FROM "_SYS_BIC"."Models/CV_EMP"

( PLACEHOLDER."$$P_DEPARTMENT$$" => :P_DEPARTMENT)

WHERE (("COUNTRY" IN (:V_COUNTRY)))

GROUP BY "EMPNAME", "EMPID","COUNTRY",'REGION","DEPARTMENT"

END;

I am able to pass single values to the procedure and it works fine.

My question is how to pass multiple values to the variable and input parameter?

Regards,

Rohit

[Former Member]

Hi Rohit,

If  you want to use operators like BETWEEN , GREATER THAN or LESSER THAN to filter a particular column in calculation view passing multiple values, then you need to create 2 input parameters

For example,

1) Create 2 input parameters IN_FROM_DATE and IN_TO_DATE in CV

2) Apply filter to your column ( CREATED_ON ) in CV and select operator  BETWEEN

3) Choose the above input parameters in both fields to filter values like range.

4) inside your procedure you can call SQL as like below passing the user parameters.

SELECT  EMPID

FROM "_SYS_BIC"."Models/CV_EMP"

( PLACEHOLDER."$$IN_FROM_DATE$$" => : P_DATE1)

( PLACEHOLDER."$$IN_TO_DATE$$" => : P_DATE2)

WHERE (("COUNTRY" IN (:V_COUNTRY)))

GROUP BY "EMPID",

and for variable, your example itself shows how multiple values possible for a variable as you have used IN operator. Likewise you can also use BETWEEN instead to bring range values.

Regards

Siva