GRChandrashekar
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.AFFILIATEM EMBERNO
|| AFFILIATECLUB.AFFILIATECLU BNO
|| MEMBER.MEMBERNO
|| ROOM.ROOMNO AS ID,
CORPORATE.NAME
|| EMPLOYEE.NAME
|| AFFILIATEMEMBER.NAME
|| AFFILIATECLUB.NAME
|| MEMBER.NAME
|| ROOM.ROOMNO AS NAME,
TRANSACTIONMODE.DESCRIPTIO N AS TRANSACTIONMODE,
POINTOFSALEBILL.BILLAMOUNT , POINTOFSALEBILL.TAXAMOUNT,
POINTOFSALEBILL.NETAMOUNT, POINTOFSALEBILL.CREATEDUSE R_ID,
POINTOFSALEBILL.MODIFIEDUS ER_ID, POINTOFSALEBILL.BEARER_ID,
POINTOFSALE.DESCRIPTION AS POINTOFSALE,
POINTOFSALE.DEPARTMENT_ID, POINTOFSALEBILL.POINTOFSAL E_ID,
POINTOFSALEBILL.TRANSACTIO NMODE_ID, POINTOFSALEBILL.FLAG,
POINTOFSALEBILL.TRANSACTIO NTYPE_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.POINTOFSAL E_ID
INNER JOIN TRANSACTIONMODE
ON TRANSACTIONMODE.TRANSACTIO NMODE_ID =
POINTOFSALEBILL.TRANSACTIO NMODE_ID
INNER JOIN TRANSACTIONTYPE
ON TRANSACTIONTYPE.TRANSACTIO NTYPE_ID =
POINTOFSALEBILL.TRANSACTIO NTYPE_ID
LEFT JOIN CORPORATE
ON CORPORATE.CORPORATE_ID = POINTOFSALEBILL.CORPORATE_ ID
LEFT JOIN EMPLOYEE
ON EMPLOYEE.EMPLOYEE_ID = POINTOFSALEBILL.EMPLOYEE_I D
LEFT JOIN AFFILIATEMEMBER
ON AFFILIATEMEMBER.AFFILIATEM EMBER_ID =
POINTOFSALEBILL.AFFILIATEM EMBER_ID
LEFT JOIN AFFILIATECLUB
ON AFFILIATECLUB.AFFILIATECLU B_ID =
POINTOFSALEBILL.AFFILIATEC LUB_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.TRANSACTI ONMODE_ID)
)
AND ( P_POINTOFSALE IS NULL
OR P_POINTOFSALE IN (POINTOFSALEBILL.POINTOFSA LE_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.POINTOFSA LE_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 !
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
CORPORATE.CORPORATENO
|| EMPLOYEE.EMPLOYEENO
|| AFFILIATEMEMBER.AFFILIATEM
|| AFFILIATECLUB.AFFILIATECLU
|| MEMBER.MEMBERNO
|| ROOM.ROOMNO AS ID,
CORPORATE.NAME
|| EMPLOYEE.NAME
|| AFFILIATEMEMBER.NAME
|| AFFILIATECLUB.NAME
|| MEMBER.NAME
|| ROOM.ROOMNO AS NAME,
TRANSACTIONMODE.DESCRIPTIO
POINTOFSALEBILL.BILLAMOUNT
POINTOFSALEBILL.NETAMOUNT,
POINTOFSALEBILL.MODIFIEDUS
POINTOFSALE.DESCRIPTION AS POINTOFSALE,
POINTOFSALE.DEPARTMENT_ID,
POINTOFSALEBILL.TRANSACTIO
POINTOFSALEBILL.TRANSACTIO
DECODE (TRANSACTIONTYPE.ISDEBIT, '1', 'D', NULL) AS ISDEBIT,
DECODE (TRANSACTIONTYPE.ISCREDIT,
FROM POINTOFSALEBILL INNER JOIN POINTOFSALE
ON POINTOFSALE.POINTOFSALE_ID
INNER JOIN TRANSACTIONMODE
ON TRANSACTIONMODE.TRANSACTIO
POINTOFSALEBILL.TRANSACTIO
INNER JOIN TRANSACTIONTYPE
ON TRANSACTIONTYPE.TRANSACTIO
POINTOFSALEBILL.TRANSACTIO
LEFT JOIN CORPORATE
ON CORPORATE.CORPORATE_ID = POINTOFSALEBILL.CORPORATE_
LEFT JOIN EMPLOYEE
ON EMPLOYEE.EMPLOYEE_ID = POINTOFSALEBILL.EMPLOYEE_I
LEFT JOIN AFFILIATEMEMBER
ON AFFILIATEMEMBER.AFFILIATEM
POINTOFSALEBILL.AFFILIATEM
LEFT JOIN AFFILIATECLUB
ON AFFILIATECLUB.AFFILIATECLU
POINTOFSALEBILL.AFFILIATEC
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,
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.TRANSACTI
)
AND ( P_POINTOFSALE IS NULL
OR P_POINTOFSALE IN (POINTOFSALEBILL.POINTOFSA
)
ORDER BY BILLDATE ASC;
END SP_SALESREPORT;
/
Problem
I am having serious problem in passing multiple POINTOFSALE_ID in the following line
IN (POINTOFSALEBILL.POINTOFSA
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
@
Guess I require more explanation in detail
Guess I require more explanation in detail
ASKER
@ johanntagle:
Guess I require more explanation in detail
Guess I require more explanation in detail
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;