Bart Friederichs wrote:
> Hi all,
>
> I want to receive some calculated fields in a SELECT statement, of which
> some are made with results of previous calculations, like:
>
> SELECT
> if(invoice1<>"", invoiceamount1, 0)+
> if(invoice2<>"", invoiceamount2, 0)
> AS paidamount,
> totalamount-paidamount
> AS neededamount;
>
> Where invoice1 and invoice2 are text fields and invoiceamount1 and
> invoiceamount2 are numerical fields.
>
> I know this doesn't work, but is there a way to do this? It has to work
> in one query, to simplify the rest of the implementation.
>
> The only option I can think of is repeating the if() statements.
>
> TIA
> Bart

User variables may be what you want:

SELECT
@PAIDAMOUNT := if(invoice1<>"", invoiceamount1, 0)+
if(invoice2<>"", invoiceamount2, 0)
AS paidamount,
totalamount-@PAIDAMOUNT
AS neededamount;

Remember that each user variable is evaluated only once per record.

ciao
gmax

--
_ _ _ _
(_|| | |(_|><
_|
http://gmax.oltrelinux.com