Result consisted of more than one row Error 1172 mysql

Question

Hello im having a hard time with this stored procedure. im getting the error: Result consisted of more than one row.

here is my stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `dss`.`COSTRET` $$
CREATE DEFINER=`dwadmin`@`192.168.%.%` PROCEDURE `COSTRET`( TDATE DATE)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE ls_id VARCHAR(8);
    DECLARE ld_cost DECIMAL(10,4);
      DECLARE ld_retail DECIMAL(10,4);
    DECLARE cur1 CURSOR FOR SELECT DISTINCT `id` FROM `prod_performance` WHERE `psc_week` = TDATE;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  -- Get the Cost
  CREATE TEMPORARY TABLE IF NOT EXISTS `prod_itemcost`
    SELECT DISTINCTROW `itemcode` ID, `mlist` COST
    FROM (SELECT `itemcode`, `pceffdate`, `mlist`
        FROM `purchcost` a
        where `pceffdate` = (SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
        AND z.`pceffdate` <= TDATE)) tb
    ORDER BY `itemcode`;

    OPEN cur1;
    REPEAT
      FETCH cur1 INTO ls_id;
      IF NOT done THEN
            SELECT DISTINCTROW `cost` INTO ld_cost FROM `prod_itemcost` WHERE id = ls_id;

        UPDATE LOW_PRIORITY `prod_performance` SET `current_cost` = ld_cost WHERE `psc_week` = TDATE and `id` = ls_id;
      END IF;
    UNTIL done END REPEAT;
    CLOSE cur1;

   -- Destroy Temporary Tables
   DROP TEMPORARY TABLES IF EXISTS `prod_itemcost`;
END $$

DELIMITER ;

Any solutions and recommendations are much appreciated!

Answer 1

I'd say the problem is here :

SELECT DISTINCTROW `cost` INTO ld_cost FROM `prod_itemcost` WHERE id = ls_id;

and caused by this returning more than one row. How you solve it depends on your requirements. Does the existence of multiple rows imply the database is in need of some cleaning, for example? Or should you be taking the first value of 'cost', or perhaps the sum of all 'cost' for id = ls_id?

Edit :

Your INTO clause is attempting to write multiple rows to a single variable. Looking at your SQL, I'd say the underlying problem is that your initial query to pull back just the latest cost for each ID is being hamstrung by duplicates of pceffdate. If this is the case, this SQL :

SELECT DISTINCTROW `itemcode` ID, `mlist` COST
    FROM (SELECT `itemcode`, `pceffdate`, `mlist`
        FROM `purchcost` a
        where `pceffdate` = (SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
        AND z.`pceffdate` <= TDATE)) tb

will return more rows than just this :

SELECT DISTINCTROW `itemcode` ID
    FROM (SELECT `itemcode`, `pceffdate`, `mlist`
        FROM `purchcost` a
        where `pceffdate` = (SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
        AND z.`pceffdate` <= TDATE)) tb