当前位置: 动力学知识库 > 问答 > 编程问答 >

stored procedures - Result consisted of more than one row Error 1172 mysql

问题描述:

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!

网友答案:

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
网友答案:

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?

网友答案:

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.

网友答案:

Here is the correct solution. Take a look at my answer to this question MySQL Error 1172 - Result consisted of more than one row

Thank you.

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: