Invalid column name error when using alias in HAVING clause

Data and Databases
1

I’m running into an issue with a SQL query where I’m getting an error about an invalid column name when trying to use a calculated field alias in the HAVING clause.

Here’s my query:

SELECT 
    StoreId,
    (6371 * 
     acos(cos(radians(40.7128)) * 
     cos(radians(Lat)) * 
     cos(radians(Lng) - 
     radians(-74.0060)) + 
     sin(radians(40.7128)) * 
     sin(radians(Lat)))) AS proximity
FROM 
    [dbo].[StoreLocations]
HAVING 
    proximity < 50
ORDER BY 
    proximity

The error I’m getting is:

Msg 207, Level 16, State 1, Line 13
Invalid column name ‘proximity’

Why can’t SQL Server recognize the alias I created in the SELECT statement when I try to use it in the HAVING clause? Is there a way to fix this without repeating the entire calculation?

2

Oh interesting! I’ve hit this wall before - super frustrating. What SQL Server version are you running? Newer versions handle the logical processing order differently. Try wrapping it in a subquery instead of a CTE - might work better for your case.

3

This happens because of how SQL Server processes queries. The HAVING clause runs before SELECT finalizes column aliases, so your ‘proximity’ alias isn’t available yet when HAVING executes. Actually, you should be using WHERE instead of HAVING since you don’t have GROUP BY. HAVING is for filtering grouped results, not individual rows. Try this instead: WITH LocationCalculation AS ( SELECT StoreId, (6371 * acos(cos(radians(40.7128)) * cos(radians(Lat)) * cos(radians(Lng) - radians(-74.0060)) + sin(radians(40.7128)) * sin(radians(Lat)))) AS proximity FROM [dbo].[StoreLocations] ) SELECT StoreId, proximity FROM LocationCalculation WHERE proximity < 50 ORDER BY proximity This CTE approach calculates the distance once and lets you reference the alias in WHERE and ORDER BY without repeating that complex formula.

4

yeah, this trips up evryone in SQL Server! you can’t use aliases in HAVING cause it gets evaluted before SELECT. just repeat the calculation in the HAVING clause or use that CTE approach someone mentioned - both work great.