Link to home
Create AccountLog in
Avatar of GRChandrashekar
GRChandrashekarFlag for India

asked on

Passing IN for stored Proc

Hi I have the following SP which works fine

CREATE OR REPLACE PROCEDURE BCLUB1868.SP_SALESREPORT (
   P_FROMDATE                  VARCHAR2,
      P_TODATE                  VARCHAR2,
   P_MEMBERID                  VARCHAR2,
   P_EMPLOYEEID                VARCHAR2,
   P_CORPORATEID               VARCHAR2,
   P_AFFILIATEMEMBERID         VARCHAR2,
   P_AFFILIATECLUBID           VARCHAR2,
   P_ROOMID                    VARCHAR2,
   P_DEPARTMEMNT               VARCHAR2,
   P_TRANSACTIONTYPE           VARCHAR2,
   P_POINTOFSALE               VARCHAR2,
   O_RESULTSET           OUT   TYPES.CURSORTYPE
)
AS
BEGIN
   OPEN O_RESULTSET FOR
      SELECT   POINTOFSALEBILL.BILLDATE,
               NVL (REFERENCENUMBER, POINTOFSALEBILL.BILLNUMBER) BILLNUMBER,
                  CORPORATE.CORPORATENO
               || EMPLOYEE.EMPLOYEENO
               || AFFILIATEMEMBER.AFFILIATEMEMBERNO
               || AFFILIATECLUB.AFFILIATECLUBNO
               || MEMBER.MEMBERNO
               || ROOM.ROOMNO AS ID,
                  CORPORATE.NAME
               || EMPLOYEE.NAME
               || AFFILIATEMEMBER.NAME
               || AFFILIATECLUB.NAME
               || MEMBER.NAME
               || ROOM.ROOMNO AS NAME,
               TRANSACTIONMODE.DESCRIPTION AS TRANSACTIONMODE,
               POINTOFSALEBILL.BILLAMOUNT, POINTOFSALEBILL.TAXAMOUNT,
               POINTOFSALEBILL.NETAMOUNT, POINTOFSALEBILL.CREATEDUSER_ID,
               POINTOFSALEBILL.MODIFIEDUSER_ID, POINTOFSALEBILL.BEARER_ID,
               POINTOFSALE.DESCRIPTION AS POINTOFSALE,
               POINTOFSALE.DEPARTMENT_ID, POINTOFSALEBILL.POINTOFSALE_ID,
               POINTOFSALEBILL.TRANSACTIONMODE_ID, POINTOFSALEBILL.FLAG,
               POINTOFSALEBILL.TRANSACTIONTYPE_ID, POINTOFSALEBILL.CARDID,
               DECODE (TRANSACTIONTYPE.ISDEBIT, '1', 'D', NULL) AS ISDEBIT,
               DECODE (TRANSACTIONTYPE.ISCREDIT, '1', 'C', NULL) AS ISCREDIT
          FROM POINTOFSALEBILL INNER JOIN POINTOFSALE
               ON POINTOFSALE.POINTOFSALE_ID = POINTOFSALEBILL.POINTOFSALE_ID
               INNER JOIN TRANSACTIONMODE
               ON TRANSACTIONMODE.TRANSACTIONMODE_ID =
                                           POINTOFSALEBILL.TRANSACTIONMODE_ID
               INNER JOIN TRANSACTIONTYPE
               ON TRANSACTIONTYPE.TRANSACTIONTYPE_ID =
                                           POINTOFSALEBILL.TRANSACTIONTYPE_ID
               LEFT JOIN CORPORATE
               ON CORPORATE.CORPORATE_ID = POINTOFSALEBILL.CORPORATE_ID
               LEFT JOIN EMPLOYEE
               ON EMPLOYEE.EMPLOYEE_ID = POINTOFSALEBILL.EMPLOYEE_ID
               LEFT JOIN AFFILIATEMEMBER
               ON AFFILIATEMEMBER.AFFILIATEMEMBER_ID =
                                           POINTOFSALEBILL.AFFILIATEMEMBER_ID
               LEFT JOIN AFFILIATECLUB
               ON AFFILIATECLUB.AFFILIATECLUB_ID =
                                             POINTOFSALEBILL.AFFILIATECLUB_ID
               LEFT JOIN MEMBER ON MEMBER.MEMBER_ID =
                                                    POINTOFSALEBILL.MEMBER_ID
               LEFT JOIN ROOM ON ROOM.ROOM_ID = POINTOFSALEBILL.ROOM_ID
         WHERE POINTOFSALEBILL.FLAG = 0
           AND TO_CHAR (POINTOFSALEBILL.BILLDATE, 'DD/MM/YYYY')
                  BETWEEN NVL (P_FROMDATE, TO_CHAR (BILLDATE, 'DD,MM,YYYY'))
                      AND NVL (P_TODATE, TO_CHAR (BILLDATE, 'DD/MM/YYYY'))
           AND (P_MEMBERID IS NULL OR P_MEMBERID = MEMBER_ID)
           AND (P_EMPLOYEEID IS NULL OR P_EMPLOYEEID = EMPLOYEE_ID)
           AND (P_CORPORATEID IS NULL OR P_CORPORATEID = CORPORATE_ID)
           AND (   P_AFFILIATEMEMBERID IS NULL
                OR P_AFFILIATEMEMBERID = AFFILIATEMEMBER_ID
               )
           AND (   P_AFFILIATECLUBID IS NULL
                OR P_AFFILIATECLUBID = AFFILIATECLUB_ID
               )
           AND (P_ROOMID IS NULL OR P_ROOMID = ROOM_ID)
           AND (   P_DEPARTMEMNT IS NULL
                OR P_DEPARTMEMNT IN (POINTOFSALE.DEPARTMENT_ID)
               )
           AND (   P_TRANSACTIONTYPE IS NULL
                OR P_TRANSACTIONTYPE IN (POINTOFSALEBILL.TRANSACTIONMODE_ID)
               )
           AND (   P_POINTOFSALE IS NULL
                OR P_POINTOFSALE IN (POINTOFSALEBILL.POINTOFSALE_ID)
               )
      ORDER BY BILLDATE ASC;
END SP_SALESREPORT;
/

Problem
I am having serious problem in passing multiple POINTOFSALE_ID in the following line
IN (POINTOFSALEBILL.POINTOFSALE_ID)

If I pass  POINTOFSALE_ID as (1,3,5,6,) i get an error invalid number ! I am trying from couple of months to understand to how to pass IN in SP but no luck. Had posted earlier for diff SP here also but no solution was given !


ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
One way to do it is to use a user defined pl/sql array, populate that based on the string passed to the procedure, then use it via "IN table(cast (array_name))".  Something like:

Create the user-defined type:

create or replace type g_array as table of number;

Then in your stored proc you have
PROCEDURE MyProc.....
  p_list g_array;
  <etc>
BEGIN
  p_list := g_array('01','02','03'); /*you need to replace this part with a procedure to split the input string into the array.  I'll see if I can post something as a followup*/
  OPEN O_RESULTSET FOR FOR
     ...
     AND (   P_POINTOFSALE IS NULL
                OR P_POINTOFSALE IN (select * from TABLE(CAST(p_list AS g_array)))
    .....
END;
Avatar of GRChandrashekar

ASKER

@
Guess I require more explanation in detail
@ johanntagle:
Guess I require more explanation in detail