Referring to a Column Alias in a WHERE Clause
SqlSql ServerTsqlSql Server-2005Sql Problem Overview
SELECT logcount, logUserID, maxlogtm
, DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE daysdiff > 120
I get
> "invalid column name daysdiff".
Maxlogtm is a datetime field. It's the little stuff that drives me crazy.
Sql Solutions
Solution 1 - Sql
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)
Normally you can't refer to field aliases in the WHERE
clause. (Think of it as the entire SELECT
including aliases, is applied after the WHERE
clause.)
But, as mentioned in other answers, you can force SQL to treat SELECT
to be handled before the WHERE
clause. This is usually done with parenthesis to force logical order of operation or with a Common Table Expression (CTE):
Parenthesis/Subselect:
SELECT
*
FROM
(
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
) as innerTable
WHERE daysdiff > 120
Or see Adam's answer for a CTE version of the same.
Solution 2 - Sql
If you want to use the alias in your WHERE
clause, you need to wrap it in a sub select, or CTE:
WITH LogDateDiff AS
(
SELECT logcount, logUserID, maxlogtm
, DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
)
SELECT logCount, logUserId, maxlogtm, daysdiff
FROM LogDateDiff
WHERE daysdiff > 120
Solution 3 - Sql
The most effective way to do it without repeating your code is use of HAVING instead of WHERE
SELECT logcount, logUserID, maxlogtm
, DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
HAVING daysdiff > 120
Solution 4 - Sql
If you don't want to list all your columns in CTE, another way to do this would be to use outer apply
:
select
s.logcount, s.logUserID, s.maxlogtm,
a.daysdiff
from statslogsummary as s
outer apply (select datediff(day, s.maxlogtm, getdate()) as daysdiff) as a
where a.daysdiff > 120
Solution 5 - Sql
How about using a subquery(this worked for me in Mysql)?
SELECT * from (SELECT logcount, logUserID, maxlogtm
, DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary) as 'your_alias'
WHERE daysdiff > 120
Solution 6 - Sql
HAVING works in MySQL according to documentation:
> The HAVING clause was added to SQL because the WHERE keyword could not > be used with aggregate functions.
Solution 7 - Sql
You could refer to column alias but you need to define it using CROSS/OUTER APPLY
:
SELECT s.logcount, s.logUserID, s.maxlogtm, c.daysdiff
FROM statslogsummary s
CROSS APPLY (SELECT DATEDIFF(day, s.maxlogtm, GETDATE()) AS daysdiff) c
WHERE c.daysdiff > 120;
Pros:
- single definition of expression(easier to maintain/no need of copying-paste)
- no need for wrapping entire query with CTE/outerquery
- possibility to refer in
WHERE/GROUP BY/ORDER BY
- possible better performance(single execution)
Solution 8 - Sql
Came here looking something similar to that, but with a CASE WHEN, and ended using the where like this: WHERE (CASE WHEN COLUMN1=COLUMN2 THEN '1' ELSE '0' END) = 0
maybe you could use DATEDIFF
in the WHERE
directly.
Something like:
SELECT logcount, logUserID, maxlogtm
FROM statslogsummary
WHERE (DATEDIFF(day, maxlogtm, GETDATE())) > 120
Solution 9 - Sql
For me, the simplest way to use ALIAS in WHERE class is to create a subquery and select from it instead.
Example:
WITH Q1 AS (
SELECT LENGTH(name) AS name_length,
id,
name
FROM any_table
)
SELECT id, name, name_length form Q1 where name_length > 0
Cheers, Kel