MySQL Forums
Forum List  »  Stored Procedures

open cursor error
Posted by: Sujata Edekar
Date: January 23, 2018 03:13PM

CREATE DEFINER=`root`@`localhost` PROCEDURE `hrDaySal`(in fromDate date,in toDate date)
BEGIN
declare id int;
declare Vweekid int ;
declare Vfromdate date;
declare Vtodate date;
declare Vemployeeid ,Vemptypeid tinyint unsigned;
declare VhrDay,Vsalarypaid decimal(5,2);
DECLARE m1,m2 date;
DECLARE cur1 CURSOR FOR
SELECT weekid,fromdate,todate,employeeid,
emptypeid,hrDay,salpaid from salaryCalc;

drop temporary table if exists hrDayWork;
create temporary table hrDayWork as
(select rounD((sum(time_to_sec(hrworked)))/3600,2) hrWork,
sec_to_time(sum(time_to_sec(hrworked))) as hrs,sum(WorkDay)
as dayWork,rs.employeeid ,rs.emptypeid,rs.fname,rs.lname,
fromdate ,todate ,
WEEK(fromdate,1) as weekId
from
(SELECT e.employeeid,e.empTypeId,e.Fname,e.lname ,
loginDate,
case
when timediff(outtime,intime) > '04:00:00' then '1'
when timediff(outtime,intime)<= '04:00:00' then '0.5'end
as WorkDay,
SEC_TO_TIME(SUM(TIME_TO_SEC(outTime) - TIME_TO_SEC(intime)))
AS hrWorked
FROM emplogin el, employee e where
el.employeeId=e.employeeId and
loginDate between fromDate and toDate
GROUP BY loginid)rs group by employeeid );
/* Table fo salry */


drop temporary table if exists salaryCalc;

create temporary table salaryCalc as
( select hd.weekid weekid ,hd.fromdate fromdate ,
hd.todate todate,ew.employeeId employeeid,
hd.emptypeid emptypeid,hd.fname fname,hd.lname lname,
case (hd.emptypeID )
when 1 then (hrWork)
when 2 then (dayWork) end as hrDay,
case (hd.emptypeID)
when 1 then round((hrWork * ew.totSal))
when 2 then round((dayWork* ew.totSal))end as salpaid
from hrdayWork hd,empwages ew
where hd.employeeId=ew.employeeId );

SELECT MAX(todate) into m1 FROM salary ;
select max(todate) into m2 from salaryCalc ;

open cur1;
/*
select weekid,fromdate,todate,employeeid,
emptypeid,hrDay,salpaid into
@Vweekid,@Vfromdate,@Vtodate,@Vemployeeid,@Vemptypeid,
@VhrDay,@Vsalarypaid from salaryCAlc;

select count(*) into @id from salary;

insert into salary values
(@id+1,Vweekid,Vfromdate,Vtodate,Vemployeeid,
Vemptypeid,VhrDay,Vsalarypaid) ;*/

if (m2>m1|| m1=null) then
write_loop: LOOP
FETCH cur1 INTO
Vweekid,Vfromdate,Vtodate,Vemployeeid,
Vemptypeid,VhrDay,Vsalarypaid ;
INSERT INTO salary
(weekid,fromdate,todate,employeeid,
employeetypeid,hrDay,salpaid) values
(Vweekid,Vfromdate,Vtodate,Vemployeeid,
Vemptypeid,VhrDay,Vsalpaid);

END LOOP;
ELSE
close cur1;
END IF;

CLOSE cur1;

END

This is my code but giving error on execution 'cursor isnt open.
Can i open a cursor on temporary table Please seeking help for this topic

Options: ReplyQuote


Subject
Views
Written By
Posted
open cursor error
2633
January 23, 2018 03:13PM
536
January 23, 2018 03:54PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.