Hi All,
I have problem in creating procedure, here is my code and it giving the above ERROR.
| 32 | 6 | PLS-00428: an INTO clause is expected in this SELECT statement |
create or replace procedure xxnmcweb.xxnmc_get_max_leave_end_date
(
p_emp_num in varchar2,
p_rejoin_date in date,
p_recordset out sys_refcursor
)
as
begin
open p_recordset for
select date_end max_leave_end_date from(
select employee_number,grp, min(date_start) date_start, max(date_end) date_end
from (select employee_number ,date_start,date_end,days,
sum(sog) over (order by date_end) grp
-- sum(sog) over (order by date_end rows between unbounded preceding and unbounded following) last_grp
from(select papf.employee_number ,date_start, date_end, absence_days days,
lag(date_end,1) over (order by date_end) thomas,
case when lag(date_end,1,date_start) over (order by date_end) != date_start - 1
then 1
end sog
from per_absence_attendances paba ,per_all_people_f papf
where papf.person_id = paba.person_id
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)
and papf.employee_number = p_emp_num ) ) group by employee_number,grp order by grp)
where p_rejoin_date between date_start and date_end;
exception
when no_data_found then
select max(paa.date_end) max_leave_end_date
from per_all_people_f papf
,per_absence_attendances paa
where papf.person_id = paa.person_id
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)
and employee_number = p_emp_num
and paa.date_start <= p_rejoin_date;
end xxnmc_get_max_leave_end_date;
/
Please help me , thanks