Привет мне приходится нелегко с этой хранимой процедурой. Я получаю ошибку: Результат состоял больше чем из одной строки.
вот моя хранимая процедура:
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 ;
Любые решения и рекомендации очень ценятся!
Проблема должна быть в этой строке
SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
AND z.`pceffdate` <= TDATE
. Должно быть, она возвращает более 1 строки. Таким образом, СУБД пытается установить несколько значений для одного и того же, что, конечно, невозможно.
Может быть, вам нужно что-то еще в предложении WHERE?
Проблема в том, что
SELECT DISTINCTROW `itemcode` ID, `mlist` COST
может хранить несколько стоимостей для каждого ID, и поэтому
SELECT DISTINCTROW `cost` INTO ld_cost FROM `prod_itemcost` WHERE id = ls_id;
может возвращать несколько строк для каждого ID.
Например, если purchasecost содержит следующее:
itemcode mlist pceffdate
1 10.99 10-apr-2009
1 11.99 10-apr-2009
1 9.99 09-apr-2009
Тогда временная таблица prod_itemcost будет содержать:
itemcode mlist
1 10.99
1 11.99
Это оба значения, которые действовали на последнюю дату pceffdate для данного кода товара.
Тогда возникнет проблема с выбором mlist в ld_cost для артикула 1, поскольку есть два совпадающих значения, а скаляр ld_cost может хранить только одно.
Вам действительно нужно посмотреть на данные в purchcost. Если для одного товара возможно наличие более одной записи с разными значениями mlist для одной и той же даты/времени, то вам нужно решить, как это должно быть обработано. Возможно, брать наибольшее значение, или наименьшее значение, или любое значение. Или, возможно, это ошибка в данных.
Я бы сказал, что проблема здесь :
SELECT DISTINCTROW `cost` INTO ld_cost FROM `prod_itemcost` WHERE id = ls_id;
и вызвана тем, что это возвращает более одной строки. Как вы ее решите, зависит от ваших требований. Означает ли наличие нескольких строк, что база данных нуждается в некоторой очистке, например? Или вы должны взять первое значение 'cost', или, возможно, сумму всех 'cost' для id = ls_id?
Edit :
Ваше предложение INTO пытается записать несколько строк в одну переменную. Глядя на ваш SQL, я бы сказал, что основная проблема в том, что ваш первоначальный запрос, чтобы получить только последнюю стоимость для каждого ID, затруднен дубликатами pceffdate. Если это так, то этот 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
вернет больше строк, чем просто этот :
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