Hi,
I've got a proc in which i have a SELECT with a GROUP BY (see code). When I run the statement on it's own (and replace p_fiscal_end with '31-OCT-13') everything works fine. But If I try to execute the PKG and pass '31-OCT-13' has a parameter I get the ORA-00979: not a GROUP BY expression error....
Any clue has to why I get this error in the PKG and not when run as i single SELECT statement.
[code]
SELECT jd_types,
jd_count
FROM (SELECT /*+ INDEX(JD JD_PK) */
CASE
WHEN (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) < 30
THEN 1
WHEN (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) > 29
AND (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) < 60
THEN 2
WHEN (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) > 59
AND (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) < 90
THEN 3
WHEN (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) > 89
THEN 4
END jd_types,
COUNT (DISTINCT jd.system_file_number) jd_count
FROM job_definitions jd,
requests_for_opinion rfo,
hrcc h,
employment_locations el,
opinion_processes op
WHERE op.decision_id = 8
AND op.system_file_number = jd.system_file_number
AND op.generates_process_id is null
AND jd.request_for_opinion_id = rfo.request_for_opinion_id
AND rfo.request_type_id = 3
AND rfo.hrcc_id = h.hrcc_id
AND h.hrc_number = 1877
AND jd.system_file_number = el.system_file_number
AND el.primary_location_yn = 'Y'
GROUP BY CASE
WHEN (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) < 30
THEN 1
WHEN (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) > 29
AND (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) < 60
THEN 2
WHEN (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) > 59
AND (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) < 90
THEN 3
WHEN (to_date(p_fiscal_end) - trunc (jd.date_created) + 1) > 89
THEN 4
END)
WHERE jd_types IS NOT NULL;
[/code]
Thanks !