Conditionals on derived columns

Hi,

Here's my current query, which throws an error that "AgeCalc" is an invalid column in the WHERE clause:

----------
SELECT
.
.
.,
AgeCalc =
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END

FROM
ResidentData B

WHERE
(AgeCalc >= 18)
----------

How do I do conditionals on the "AgeCalc" derived column?

Thanks.How do I do conditionals on the "AgeCalc" derived column?

Thanks.

You have to write the expression over again:
WHERE
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END >= 18

Alternatively, write a view that includes your derived column and then you can use your column name in an expression.

I don't recommend using CASE statements in WHERE clauses. It can result in sub-optimal query execution plans.

Regards,

hmscott|||Thanks for your help - I will test the solution and see what the performance is like.

The current situation does not allow me to consider creating views, so I'll have to stick to keeping the query similar to the way it already is.|||select *
from (
SELECT ...
, AgeCalc =
CASE WHEN dateadd(year
, datediff(year, B.DOB, B.DateIn)
, B.DOB) > B.DateIn
THEN datediff(year, B.DOB, B.DateIn) - 1
ELSE datediff(year, B.DOB, B.DateIn)
END
FROM ResidentData B
) as T
WHERE AgeCalc >= 18|||Thanks guys. Both solutions worked well. I will use the second one since it's about half a second faster.