[sql] Referring to a Column Alias in a WHERE Clause

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.

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

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

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

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

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

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;

DBFiddle Demo

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)

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.

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