MySQL Result consisted of more than one row

Question

Hi there im just new here and i have a question regarding my error on mysql it said Result consisted of more than one row. I have no idea how to solve this your help is much appreciated!

 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 ;

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

Answer 2

The problem is that

SELECT DISTINCTROW `itemcode` ID, `mlist` COST

could store multiple costs against each ID, and so

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

could return multiple rows for each id.

For example, if purchcost contained the following:

itemcode   mlist   pceffdate
1          10.99   10-apr-2009
1          11.99   10-apr-2009
1           9.99   09-apr-2009

Then temporary table prod_itemcost would contain:

itemcode   mlist
1          10.99
1          11.99

These both being values that were in effect on the most recent pceffdate for that itemcode.

This would then cause a problem with selecting mlist into ld_cost for itemcode 1 because there are two matching values, and the scalar ld_cost can only hold one.

You really need to look at the data in purchcost. If it is possible for 1 item to have more than one entry with different mlist values for the same date/datetime, then you need to decide how that should be handled. Perhaps take the highest value, or the lowest value, or any value. Or perhaps this is an error in the data.