Re-using a calculated column

[johnnyb]

Hi,

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?

[guelphdad]

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.

[johnnyb]

Hi,

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:

Table: listings
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
FROM listings
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.

[guelphdad]

so change the WHERE to a HAVING

[johnnyb]

Hi,

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.