Using Aliases and/or user-defined variable

[KombatKarl]

I have a problem I have tried doing with aliases and variables and neither is working. What I am trying to do is have a column determined by an expression and use that column later in the select. The docs say that aliases can't be used in a WHERE clause, but it says nothing about the SELECT part.

Here's what I'm trying to do, first I tried with aliases:

SELECT (expr here) as col1, (expr here) as col2, (col1*col2) as col3 from.... where....

The error I get is undefined column col1. The expressions are fairly complex which is why I don't want to jumble them all together into one gigantic expression. The ecpressions involve summing and substringing. Which I may have to do but am trying to avoid.

Then I tried with variables:

SELECT (@a:=expr), (@b:=expr), (@a*@b) as col1 from... where...

This doesn't work because for some reason when it get to column 3, @a and @b don't have the values that were output in the first 2 columns. I tested this by putting @a, @b into the select clause. All rows have the values from the first 2 columns from the last record in the result set, NOT for the current record. The last record's values for a and b are 688 and 1, and when I output these in the select, ALL rows have values 688 and 1. Am I missing something with regards to using variables?

I'm also thinking about how to do this with a subquery.

Thanks,
Karl

 

[TonyGroves]

It looks like the assignment of a variable happens only once in a statement; that's why @a and @b are the same in every record.

As you said, a sub-select would solve that:
[tt]
SELECT col1,col2,col1*col2 FROM
(SELECT expr1 col1,expr2 col2 FROM tbl)
[/tt]
Trouble is, you need MySQL 4.1 (currently alpha) for sub-selects.

-----
ALTER world DROP injustice, ADD peace;