Invalid Column Name for computed column?

Question

I have the following query, that works just fine if I comment out the part that is the reason for my question (as I've already done below).

Declare @mPoint As varchar(50) = 'POINT (-107.657141 41.033581)'
Declare @iRadius As int = 5000 --5km for testing. 4 results, 1 with PriorityType = 0.

SELECT FLOOR([Position].STDistance(geography::STGeomFromText(@mPoint, 4326))) AS Distance,
    CASE 
        WHEN [Type] & 64 = 64 THEN 0
        --insert other types as needed. 
        ELSE 1000 
    END AS PriorityType,
    *
FROM [tblAddress]
WHERE DeletedOn IS NULL 
    --AND Distance <= @iRadius --        <-- This is Line 13
ORDER BY PriorityType ASC, Distance ASC;

This query should filter about 1700 records in a database and based on the above (static) results, return 4 rows, of which one has a PriorityType of 0.

The thing is, Microsoft SQL Server Management Studio (SSMS for short) gives me the error

Msg 207, Level 16, State 1, Line 13
Invalid column name 'Distance'.

And a red line below the 'Distance' of AND Distance <= @iRadius.

If I comment out that line (as in the example above), I get the entire table as required. Clearly, the column is present! So why do I get the error?

You can't reference a column in your `SELECT` in your `WHERE`, as the `WHERE` is determined **before** the `SELECT`. you would need to use a CTE/Subquery or repeat the expression in your `WHERE`. — Thom A, Apr 09 '19 at 11:26
Where clause will be executed before Select clause. Distance is the Alias name you are giving in where clause. So query will not find Distance column. So try writing FLOOR([Position].STDistance(geography::STGeomFromText(@mPoint, 4326))) in where clause instead of Distance or write sub query. — Venkata Muttineni, Apr 09 '19 at 11:30

score 0 · Answer 1 · edited Apr 09 '19 at 15:04

Check this modified solution,

Declare @mPoint As varchar(50) = 'POINT (-107.657141 41.033581)' Declare @iRadius As int = 5000 SELECT FLOOR([Position].STDistance(geography::STGeomFromText(@mPoint, 4326))) AS Distance, CASE WHEN [Type] & 64 = 64 THEN 0 ELSE 1000 END AS PriorityType, * FROM [tblAddress] WHERE DeletedOn IS NULL AND FLOOR([Position].STDistance(geography::STGeomFromText(@mPoint, 4326)))<= @iRadius ORDER BY PriorityType ASC, Distance ASC;

Invalid Column Name for computed column?

1 Answers1