Result consisted of more than one row Error 1172 MySQL

Question

Hello im having a hard time with this stored procedure. I'm 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

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

The above statement causes Error Code: 1172. Result consisted of more than one row because the query returns multiple rows

To fix this error, you use the LIMIT 1 clause as follows

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

Answer 2

I got the same error below:

ERROR 1172 (42000): Result consisted of more than one row

Because I tried to store multiple row's name column values into @result which is a user-defined session variable as shown below:

mysql> SELECT * FROM person;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | David |
+----+-------+
...
mysql> SELECT name INTO @result FROM person; -- Error

But, I could store only one row's name column value into @result without error as shown below:

mysql> SELECT name INTO @result FROM person WHERE id = 1;
...
mysql> SELECT @result;  
+---------+
| @result |
+---------+
| John    |
+---------+

Answer 3

You are inserting an array in a variable instead of a single value that's why its problem occurs.

Like:

    DECLARE name varchar;

    select f_name into name from student; 

here name will accept only single name instead of multiple name;

Answer 4

There is another possibility, that is your parameter "TDATE" same as table field name in uppercase or lowercase or mixed. such as 'tdate', 'tDate', 'TDATE'.

so you should check that. I hit this before.

Answer 5

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.

Answer 6

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 7

This line

SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
    AND z.`pceffdate` <= TDATE

has got to be the problem. It must be returning more than 1 row. So, the DBMS is trying to set multiple values to the same thing, which of course it cannot do.

Do you need something else in your WHERE clause there?