1

i confused with this i try not using cursor and same get error so i back using cursor but before this day my query success and can generate but this get error please help me i already look other question but no solved my problem.

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_sallary`(IN `e_codeemployee` VARCHAR(45), IN `p_idperiod` INT)  

DECLARE p_startperiod, p_endperiod, a_attendancedate date;
declare a_checkin, a_checkout, s_workstart, s_workend time;
declare c_classid,t_workday , t_worktime, t_offs, t_absents, t_lates, t_overtimes, t_outearly, a_id, a_done, gs_tolerance, gs_sallary_hour, sr_id, chek int;
declare es_sallary, es_sallary_per_day, es_sallary_per_hour, sr_late, sr_absent, sr_outearly, sr_overtime, pr_incomesallary, pr_income_overtime, pr_deduction_absent, pr_deduction_late, pr_deduction_out_early double;
declare a_flag varchar(16);
declare a_cursor cursor for select id FROM attendance WHERE attendance_date between (SELECT start_period FROM period WHERE id = p_idperiod) AND (SELECT end_period FROM period WHERE id = p_idperiod) AND attendance.employee_code = e_codeemployee;
declare continue handler for not found set a_done = TRUE;

set chek = 0;
set gs_tolerance = 15;    
set a_done = 0;
set t_workday = 0;
set t_worktime = 0;
set t_offs = 0;
set t_absents = 0;
set t_lates = 0;
set t_overtimes = 0;
set t_outearly = 0;

select e.class_id into c_classid FROM employees e WHERE e.employee_code = e_codeemployee;
select es.sallary, es.sallary_per_day, es.sallary_per_hour, sr.late, sr.absent, sr.out_early, sr.overtime, sr.id into es_sallary, es_sallary_per_day, es_sallary_per_hour, sr_late, sr_absent, sr_outearly, sr_overtime, sr_id from employee_sallary es join sallary_rules sr on es.sallary_rule_id = sr.id;

open a_cursor;
repeat    
    fetch a_cursor into a_id;   
    if not a_done then
        select a.attendance_date, a.check_in, a.check_out, a.flag, s.work_start, s.work_end into a_attendancedate, a_checkin, a_checkout, a_flag, s_workstart, s_workend from attendance a inner join shift s on a.shift_id = s.id where a.id = a_id ;
        if (a_checkin < s_workstart) then
            set a_checkin = s_workstart;
        end if;
        if (a_checkout = null) OR (a_checkout = '00:00:00') then
            set a_checkout = s_workend;
        end if;
        if (a_flag = 'fulltime') then
            set t_workday = t_workday +1;
        elseif (a_flag = 'partime') then
            set t_worktime = t_worktime + hour(timediff(a_checkin, a_checkout));
            if (minute(timediff(a_checkin, a_checkout)) > gs_tolerance) then
                set t_worktime = t_worktime + 1;
            end if;
        elseif (a_flag = 'absent') then
            set t_absents = t_absents + 1;
        else
            set t_offs = t_offs +1;
        end if;
        if (a_checkin > s_workstart) then
            set t_lates = t_lates + hour(timediff(a_checkin, s_workstart));
            if (minute(timediff(a_checkin, s_workstart)) > gs_tolerance) then
                set t_lates = t_lates + 1;
            end if;
        else
            set t_outearly = t_outearly + hour(timediff(a_checkin, s_workstart));
            if (minute(timediff(a_checkin, s_workstart)) > gs_tolerance) then
                set t_outearly = t_outearly + 1;
            end if;
        end if;

        if (a_checkout > s_workend) then 
            set t_overtimes = t_overtimes + hour(timediff(a_checkout, s_workend));
            if (minute(timediff(a_checkout, s_workend)) > gs_tolerance) then
                set t_overtimes = t_overtimes + 1;
            end if;
        end if;     
    end if;
    until a_done
end repeat;
select p.status into chek FROM payroll p WHERE employee_code = e_codeemployee;
if (chek = 1) then
    DELETE FROM payroll  WHERE employee_code = e_codeemployee;
end if;
set pr_incomesallary = t_workday * es_sallary_per_day;
set pr_incomesallary = pr_incomesallary + (t_worktime * es_sallary_per_hour);
set pr_income_overtime = t_overtimes * sr_overtime;    
set pr_deduction_absent = t_absents * sr_absent;
set pr_deduction_late = t_lates * sr_late;
set pr_deduction_out_early = t_outearly * sr_outearly;
INSERT INTO payroll 
(employee_code, class_id, period_id, sallary, sallary_per_day, sallary_per_hour, rules_id, late, absent, out_early, overtime, t_work_day, t_work_time, t_off, t_absent, t_late, t_overtime, t_out_early, income_overtime,income_sallary, deduction_absent, deduction_late, deduction_out_early)
VALUES
(e_codeemployee, c_classid , p_idperiod,  es_sallary, es_sallary_per_day, es_sallary_per_hour,sr_id ,sr_late, sr_absent, sr_outearly, sr_overtime, t_workday ,t_worktime , t_offs, t_absents, t_lates, t_overtimes, t_outearly,pr_income_overtime, pr_incomesallary ,pr_deduction_absent,pr_deduction_late,pr_deduction_out_early);

END
  • Does mysql tell you which line number the error is in? Or which variable it was trying to set? The error could have happened while fetching salary information or attendance information. You must ensure that the select could only return one row (or if it could return more than one, then use limit 1, but that's probably lazy and could mask problems with your selection logic). – Ziggy Crueltyfree Zeitgeister Apr 28 '16 at 2:11
3

One of the "select into" statements is returning more than one result. Those statements can only return a single row.

|improve this answer|||||
0

Select into statements return one result, maybe you can use that like this:

WHILE i < 50 DO
   INSERT INTO a_table(bla, bla, bla) SELECT (bla, bla,bla) FROM b_table LIMIT i,1;
   SET i = i + 1;
END WHILE;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.