Consider the following query:
SELECT
S.StationID
, P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
I'd like to be able to add the following:
WHERE Actual_Latitude > 50
...But, I'm getting an "Invalid column name" error. Is this possible in some way?
The benefit, of course, would be that I wouldn't have to repeat the conditions in the WHERE clause.
Unfortunately, you can't.
You are creating an ALIAS for an expression, and the expression is not 'known' by that ALIAS in the 'acquisition' part of the query. Once the data is acquired, you can refer to the ALIAS in the ORDER BY because a 'derived table' has been determined.
You could, however, wrap this query in another, and use the ALIAS in the outer query. That does't provide much help with filtering though...
The 'best' option is to repeat the CASE structure for Actual_Latitude in the WHERE clause.
|||I agree with Arnie for the most part. This will work:
SELECT S.StationID , P.PoleID
--NOTE: change to this means a change to the where clause for Actual_latitude!!
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
where CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END > 50 --Actual_Latitude > 50
In 2005, I would probably try this and see how it works out. It probably will have the same plan and is a bit clearer. If this is a highly used, performance intensive operation I would consider rewriting the query to eliminate the CASE in the where clause and express it as just expressions (it could be done, I think):
WITH stationQuery AS (
SELECT S.StationID , P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
select *
from stationQuery
where actual_latitude > 50
The thing is, you are not going to get good performance no matter how you do it. Since the both of your tables in the join are tied up in the CASE expression, very unlikely to get any index utilization. You could also do it as a derived table in 2000.
