Dear Experts,
The below is SQL throwing ORA 00932 exception when I run it as a whole. But It is working fine when I execute the SQL which is in the first WITH CLAUSE and not throwing any error related to DATE.
What could be the reason.
WITH employees AS
(SELECT d.division_name
||'-'
||de.dept_name divdept,
e.hr_sys_empl_id pernr,
e.sso,
e.last_name
||','
||e.first_name name,
tc.employee_id ,
c.contract_name contract,
jc.job_class_code job_code,
COUNT(DISTINCT timecard_date) No_of_Days,
SUM(cp.units) No_of_Hours
FROM pr_timecards tc,
tk_employees e,
tk_employee_details ed,
tk_contracts c,
tk_calculated_pays cp,
tk_earning_codes ec,
tk_earning_types et,
tk_job_codes jc,
tk_divisions d,
tk_departments de
WHERE e.employee_id= ed.employee_id
AND e.employee_id = tc.employee_id
AND tc.timecard_date BETWEEN '07-JUL-2017 12.00.00' AND '07-JUL-2017 12.00.00'
-- AND '07-JUL-2017 12.00.00' BETWEEN ed.effective_date AND ed.expiry_date
AND c.contract_id = tc.contract_id
AND tc.timecard_id = cp.timecard_id
AND cp.earning_code_id = ec.earning_code_id
AND ec.earning_type_id = et.earning_type_id
AND et.earning_type IN ('ND','NG','NO','NQ','NS','NT')
AND jc.job_id = ed.job_id
AND ed.dept_id = de.dept_id
AND d.division_id = de.division_id
AND c.contract_id IN( '13205')
GROUP BY d.division_name
||'-'
||de.dept_name,
e.hr_sys_empl_id,
e.sso,
e.last_name
||','
||e.first_name,
tc.employee_id,
c.contract_name,
jc.job_class_code
HAVING COUNT(DISTINCT timecard_date)>=104
),
timecard_count AS
(SELECT e.employee_id,
c.contract_name contract,
COUNT(DISTINCT timecard_date) Total_Worked_Days
FROM employees e,
pr_timecards tc,
tk_timecard_activities tca,
tk_contracts c
WHERE e.employee_id = tc.employee_id
AND tc.contract_id = c.contract_id
AND c.contract_name = e.contract
AND tc.timecard_date BETWEEN '07-JUL-2017 12.00.00' AND '07-JUL-2017 12.00.00'
AND tc.timecard_id = tca.timecard_id
AND c.contract_id IN( '13205')
GROUP BY e.employee_id,
c.contract_name
)
SELECT e.divdept,
e.pernr,
e.sso,
e.name,
e.employee_id ,
e.contract,
e.job_code,
e.no_of_hours,
e.No_of_Days,
tc.Total_Worked_Days
FROM employees e,
timecard_count tc
WHERE e.employee_id = tc.employee_id
AND e.contract = tc.contract