# Thread: Conditionals on derived columns

1. Registered User
Join Date
Mar 2004
Posts
51

## Unanswered: 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.

2. Registered User
Join Date
Dec 2002
Posts
1,245
Originally Posted by nbozic

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

3. Registered User
Join Date
Mar 2004
Posts
51
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.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```select *
from (
SELECT ...
, AgeCalc =
, 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```

5. Registered User
Join Date
Mar 2004
Posts
51
Thanks guys. Both solutions worked well. I will use the second one since it's about half a second faster.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•