List:General Discussion« Previous MessageNext Message »
From:Michael Farr Date:April 15 1999 4:11am
Subject:balance
View as plain text  
Michael> I would like to query a table with two columns, 1st being just a
key field,
Michael> second being a debit field (float) ie

Michael> account:

Michael> service	|	debit
Michael> _______________________
Michael> 1		|	50
Michael> 2		|	100
Michael> 3		|	-30

Michael> I would like to query the table with a select and get back a
balance field
Michael> calculated at run time as well ie

Michael> select debit, balance()
Michael> from account

Michael> debit	|	debit
Michael> _______________________
Michael> 50		|	50
Michael> 100		|	150
Michael> -30		|	120

>Yes, you can do the above with an UDF function, but it will not work
>if you use 'order by' on it!

>Check the udf_example.c file and the MySQL documentation how to create 
>udf functions.

OK I read the UDF function help, and udf_example.c, and but can't see how
to access adjacent cells.  Optimistically I would like to be able to write
some code like this, but dont know how to access currentRow, or the value
at a particular coordinate.


CREATE FUNCTION balance RETURNS REAL SONAME "udf_example.so";

double balance(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
		     char *error)
{
	if(currentRow == 0)
		return value(currentRow, currentColumn-1)
	return value(currentRow-1, currentColumn) + value(currentRow, currentColum
-1)
}

Apparently my code may have to look more like this, by using the primary
key of the table to get the values I need.

double balance(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
		     char *error)
{
	if(indexId == 1)
		return indexId.debit;
	return account(indexId).debit + account(indexId - 1).balance
}
or

double balance(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
		     char *error)
{
	float total = 0;
	for(int i = 1; i<=indexId; i++)
		total += account(i).debit;
}

If anyone can tell me how to access the data structures in MySQL to get any
of those things out I should be able to finish this little problem.

Mike

Thread
balanceMichael Farr15 Apr
  • balanceMichael Widenius15 Apr
    • Re: balanceMichael Farr15 Apr
      • Re: balanceMichael Widenius16 Apr
        • balance solutionMichael Farr16 Apr