Error Code 1172. Result consisted of more than 1 row
Posted by:
John Noble
Date: March 11, 2018 05:50AM
Hi folks,
I am having a problem with a sproc. I think I know what the problem is but am unsure on how to solve it.
I am trying to query a table, create a temp table from it whilst adding a running balance to it. I have marked where I think the problem is.
CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_BankTranShowTransactions`(
IN p_vesselID varchar(7),
IN p_account varchar(1))
BEGIN
declare v_credit, v_debit, v_balance decimal(13,2);
declare v_reference varchar(25);
declare v_vesselID varchar(7);
declare v_account varchar(1);
declare v_docDate date;
declare v_pKey int;
-- Get Data for processing.
select pKey, vesselID, account, docDate, reference, credit, debit, balance from bankTran WHERE vesselID = p_vesselID and account = p_account ORDER BY docDate into v_pKey, v_vesselID, v_account, v_docDate, v_reference, v_credit, v_debit, v_balance;
-------- Above is where the problem is. I think the "INTO" part of the statement is expecting a single row. But there will always be more than 1 row.
-- Create temp table (same struct as bankTRan)
DROP TEMPORARY TABLE IF EXISTS temp_BankTran;
CREATE TEMPORARY TABLE temp_BankTran (pKey int, vesselID varchar(7), account varchar(1), docDate date, reference varchar(25), credit decimal, debit decimal, balance decimal);
-- Set opening balance to 0
set v_balance = 0;
-- Loop through results and update balance amount and insert into temp table
bankTran_loop: loop
SET v_balance = v_balance + v_credit - v_debit;
insert into temp_BankTran values (v_pKey, v_vesselID, v_account, v_docDate, v_reference, v_credit, v_debit, v_balance);
end loop;
-- Select dataset for returning
select * from temp_BankTran;
-- Delete the temp table
DROP TEMPORARY TABLE IF EXISTS temp_BankTran;
END
Can anyone advise ??
Thanks,
John