CODEWITHSUNDEEP
sqlsql-server
Tom
Tom

Reputation: 12988

SQL - invalid column name

I have the following query:

SELECT o.outcode AS lead_postcode, v.outcode AS venue_postcode, 6 * o.lat AS distance 
FROM venue_postcodes v, uk_postcodes o 
WHERE o.outcode = 'CF3' 
GROUP BY v.outcode 
HAVING SUM(distance)>100 
ORDER BY distance

This stopped working when I added the part GROUP BY v.outcode HAVING SUM(distance)>100

It says Server was unable to process request. ---> Invalid column name 'distance'.

Any ideas why?

Upvotes: 1

Views: 7205

Answers (4)

bw_üezi
bw_üezi

Reputation: 4564

The "alias" distance only just defined within the query as "6*o.lat" can not yet be used within the query but only afterwards.

alternative solution is

SELECT i.*
FROM (
  SELECT o.outcode AS lead_postcode, v.outcode AS venue_postcode, 6 * o.lat AS distance 
  FROM venue_postcodes v, uk_postcodes o 
  WHERE o.outcode = 'CF3' 
) i
GROUP BY i.outcode 
HAVING SUM(i.distance)>100 ORDER BY i.distance

Upvotes: 2

Tomáš Plešek
Tomáš Plešek

Reputation: 1492

I believe you need to use SUM(6* o.lat), because not every database server can use aliased columns in having clause (It has to do with query planning, parsing etc.). Depends on what DB you use.

Upvotes: 0

Michaël
Michaël

Reputation: 6734

Use ORDER BY 6 * o.lat. You cannot use the clause AS for an ORDER BY

Upvotes: 0

dogbane
dogbane

Reputation: 274522

distance is a column alias and you can't refer to a column alias in a HAVING clause. But you can use aliases in an ORDER BY.

Try changing to:

HAVING SUM(6 * o.lat)>100 
ORDER BY distance

Upvotes: 6

Related Questions