I am getting blank when passing a date parameter into this stored procedure. I can get back some data if I hard code the date ranges.
can someone please provide some insight on this?
CREATE OR REPLACE PROCEDURE GET_USERS(startdate_in IN DATE, enddate_in IN DATE)
IS
BEGIN
DELETE FROM TEMP_TABLE;
INSERT INTO temp_table
(ID, ROLE, DATE_USED, COUNT_S)
SELECT EMPLOYEE.CUSTOMERID as USER_ID, 'Customer' as ROLE_, to_char (EMPLOYEE.STARTEDON, 'MM-YYYY') as REQ_MONTH, count (EMPLOYEE.SUBJECT) as SUBJ_COUNT
FROM RCUSER.EMPLOYEE EMPLOYEE
WHERE EMPLOYEE.STATEID NOT IN (4,9,16,31,36) AND (EMPLOYEE.AGENTID=0) AND (EMPLOYEE.STARTEDON >= to_date(startdate_in,'yyyy-mm-dd'))
AND (EMPLOYEE.STARTEDON < to_date(enddate_in,'yyyy-mm-dd'))
Group by CUSTOMERID, to_char (EMPLOYEE.STARTEDON, 'MM-YYYY')
UNION
SELECT EMPLOYEE.PERFORMERID as USER_ID, 'Performer' as ROLE_, to_char (EMPLOYEE.STARTEDON, 'MM-YYYY') as REQ_MONTH, count (EMPLOYEE.SUBJECT) as SUBJ_COUNT
FROM RCUSER.EMPLOYEE EMPLOYEE
WHERE EMPLOYEE.PEID IN (1,4,6) AND EMPLOYEE.STATEID NOT IN (4,9,16,31,36) AND (EMPLOYEE.AGENTID=0) AND (EMPLOYEE.STARTEDON >= to_date(startdate_in,'yyyy-mm-dd'))
AND (EMPLOYEE.STARTEDON < to_date(enddate_in,'yyyy-mm-dd'))
Group by PERFORMERID, to_char (EMPLOYEE.STARTEDON, 'MM-YYYY')
UNION
SELECT EMPLOYEE.PERFORMERID as USER_ID, 'Approver' as ROLE_, to_char (EMPLOYEE.STARTEDON, 'MM-YYYY') as REQ_MONTH, count (EMPLOYEE.SUBJECT) as SUBJ_COUNT
FROM RCUSER.EMPLOYEE EMPLOYEE
WHERE EMPLOYEE.PEID IN (2,3)
AND EMPLOYEE.STATEID NOT IN (4,9,16,31,36)
AND (EMPLOYEE.AGENTID=0)
AND (EMPLOYEE.STARTEDON >= to_date(startdate_in,'yyyy-mm-dd'))
AND (EMPLOYEE.STARTEDON < to_date(enddate_in,'yyyy-mm-dd'))
Group by PERFORMERID, to_char (EMPLOYEE.STARTEDON, 'MM-YYYY');
END;