Re: Error Code 1172. Result consisted of more than 1 row

[John Noble]

Hello folks,
I managed to solve the problem.....

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, v_runningBalance decimal(13,2) DEFAULT 0;
declare v_reference varchar(25) DEFAULT "";
declare v_vesselID varchar(7) DEFAULT "";
declare v_account varchar(1) DEFAULT "";
declare v_docDate date;
declare v_pKey int DEFAULT 0;
declare v_finished int DEFAULT 0;


-- Get Cursor for processing.
DECLARE cursor_bankTran CURSOR FOR select pKey, vesselID, account, docDate, reference, credit, debit, balance from bankTran WHERE vesselID = p_vesselID and account = p_account ORDER BY docDate;


-- Declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

-- 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_runningBalance = 0;

OPEN cursor_bankTran;

get_BankTran: LOOP

FETCH cursor_bankTran INTO v_pKey, v_vesselID, v_account, v_docDate, v_reference, v_credit, v_debit, v_balance;

if v_finished = 1 THEN
LEAVE get_BankTran;
END IF;

-- Update Running balance
SET v_runningBalance = v_runningBalance + v_credit - v_debit;

-- Insert row into temp table
insert into temp_BankTran values (v_pKey, v_vesselID, v_account, v_docDate, v_reference, v_credit, v_debit, v_runningBalance);

END LOOP get_BankTRan;

CLOSE cursor_bankTran;

-- Select dataset for returning
select * from temp_BankTran;

-- Delete the temp table
DROP TEMPORARY TABLE IF EXISTS temp_BankTran;





END