MySQL Forums
Forum List  »  Stored Procedures

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Error Code 1172. Result consisted of more than 1 row
329
March 11, 2018 05:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.