Mysql stored procedure question
I have a table with a column with credit and debit.
I need to get the highest balance out of this, and I think a stored procedure is the way to do it, but I have no idea how.
I need to start on the first line, add debit, subtract credits, and store the A value.
Second line - A + debit-credit = B; A = max (A, B) Repeat the last step to the end.
Remember, I'm looking for the highest QUANTITY, not the current one, which would be just the amount (debit-credit)
source to share
It looks to me like you need the total to be A+credit-debit
, but switch them as needed.
SET @balance := 0;
SET @high := 0;
SELECT @high := GREATEST(@balance := @balance+credit-debit, @high) FROM mytable;
SELECT @high;
edit: In response to your comment about creating a stored function from this ... unlike stored procedures, stored functions must return a single value, so they cannot contain a query SELECT
, unless the query stores its result in a variable. This means that the query must be guaranteed to have one value. Below is the function I worked with because in this case you only need the MAX value @high
:
CREATE FUNCTION high_bal() RETURNS DECIMAL
BEGIN
SET @balance := 0;
SET @high := 0;
SELECT MAX(@high := GREATEST(@balance := @balance+debit_acc-credit_acc, @high))
INTO @high
FROM credit_acc where credit_used_acc=63395;
RETURN @high;
END$$
source to share
Actually, I thought I would be smart and make it a function because I only need one value in the result ...
DELIMITER $$
DROP IF EXISTS FUNCTION cc
. high_bal
$$
CREATE FUNCTION cc
. high_bal
() RETURNS DECIMAL
BEGIN
SET @balance := 0;
SET @high := 0;
SELECT @high := GREATEST(@balance := @balance+debit_acc-credit_acc, @high) FROM credit_acc where credit_used_acc=63395;
RETURN @high;
END$$
print("code sample");
DELIMITER ;
But it gives me the error "Not allowed to return a result set from a function" Which I don't understand because I just need a single value.
source to share
UDFs are just things that you insert into a SQL statement. They work in the context of each individual line (eg 40 lines, the function is executed 40 times). If you want an aggregate from a set (multiple rows), you use an SQL statement with its aggregate function (for example MAX (columname)). And this returns a "result set" which is one row with one value.
source to share