SEO

#1 (**permalink**) 02-28-2008, 07:11 AM

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

#2 (**permalink**) 02-28-2008, 07:12 AM

Bart Friederichs wrote:
> 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.

See http://dev.mysql.com/doc/refman/5.0/...th-alias.html:

Standard SQL doesn't allow you to refer to a column alias in a WHERE
clause. This is because when the WHERE code is executed, the column
value may not yet be determined. For example, the following query is
illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

> The only option I can think of is repeating the if() statements.

That's what I do. It's ugly and unsatisfying, but the SQL standard and
the MySQL implementation do not give any alternative.

Regards,
Bill K.

#3 (**permalink**) 02-28-2008, 07:12 AM

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

#4 (**permalink**) 02-28-2008, 07:12 AM

Bill Karwin wrote:
>
> See http://dev.mysql.com/doc/refman/5.0/...th-alias.html:
<snip>

That is about WHERE clauses, I knew that. My problem lies in the SELECT
part.

#5 (**permalink**) 02-28-2008, 07:12 AM

Giuseppe Maxia wrote:

> User variables may be what you want:
>
<snip>

They are. I have been looking into them, but apparently missed something.

Thanks
Bart

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

re-using aliases in SELECT