04-06-2007, 09:46 PM
I want to re-use a calculated column to make another calculated column. Like this:
SELECT (col1 + col2) AS calc1, (calc1*col3) AS calc2 FROM myfavouritetable WHERE this=working right
However, I'm getting the MySQL error Unknown column 'calc1' in 'field list'
I think this is possible in MySQL, but am I wrong?
04-06-2007, 10:11 PM
show us what you actually want to do by providing example rows and output.
you can't use a derived column name in a WHERE clause, you can use it in a HAVING clause though.
04-06-2007, 10:29 PM
I want to use it in the field list, (before the FROM).
This is for a proximity search and I am trying to make a scaled value to order the results by.
Example rows of table:
idLIST | Name | lat | lon | rank
1 |John's Svc| -63 | 44 | 2
2 |Ed's Svc |-63.5 | 44 | 0
So then I'd like to execute this query:
SELECT Name, lat, lon,
(distance calculations) AS dist,
(CASE rank WHEN 3 THEN dist*0 WHEN 2 THEN dist*0.02 WHEN 1 THEN dist*0.04 ELSE dist END) AS rankDist
WHERE (same distance calculations) > 50
The real query is more involved, (the distance calculations are pretty crazy, and I don't always have lat & lon values so I subquery from a ZIP code table), but that's basically what's going on.
04-07-2007, 07:53 AM
so change the WHERE to a HAVING
04-07-2007, 05:24 PM
The problem isn't caused by the WHERE calculation, it's caused by the SWITCH statement, (if I remove the SWITCH I have no problem, except I don't have the info I'm using the SWITCH to get).
It seems that MySQL doesn't like me using the dist column, (which is calculated), in the SWITCH statement to create a second calculated column, that's what I'm wondering about, sorry if I wasn't clear before.