Skip to Content
avatar image
Former Member

Pass multiple values to procedure

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jan 08, 2015 at 06:55 PM

    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

    Add comment
    10|10000 characters needed characters exceeded