'SQL: What am I doing wrong? adding / sum returns null
Totally MySQL newbie here. I'm a network systems management student and I have a problem with one of the exercises.
The exercise asks to create a function that returns the total amount of cash earned by a car repair shop given a specified month.
Previously I had to create two functions, one that calculates the total cost of the spare parts used in a repair (totalRecambios), and other that calculates the total cost of the worktime in a repair (ManoDeObra). Both of the functions work well.
I've created this code
DELIMITER //
CREATE FUNCTION RecaudaMes(mes int)
RETURNS decimal(6,2)
DETERMINISTIC
BEGIN
DECLARE totalmes decimal(6,2) DEFAULT 1;
DECLARE idrp INT;
DECLARE ultfila INT DEFAULT 1;
DECLARE totalconceptos decimal(6,2);
DECLARE Cursor1 CURSOR FOR SELECT idreparacion FROM reparaciones WHERE month(FechaEntrada)=mes;
DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET ultfila=0;
OPEN Cursor1;
FETCH Cursor1 INTO idrp;
WHILE ultfila=1 DO
SELECT sum(ManoDeObra(idrp)+TotalRecambios(idrp)) INTO TotalConceptos;
SET totalmes=totalmes+Totalconceptos;
FETCH Cursor1 INTO idrp;
END WHILE;
CLOSE Cursor1;
RETURN Totalmes;
END //
DELIMITER ;
My problem is that it always returns a NULL. I don't know why. I think the problem is related with the SET totalmes=totalmes+totalconceptos statement. Because if I set the function to return totalconceptos, y returns a number.
So please! What am I doing wrong???? Thanks!!!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|