Referring to a Column Alias in a WHERE Clause

Question

SELECT logcount  logUserID  maxlogtm      DATEDIFF day  maxlogtm  GETDATE    AS daysdiff FROM statslogsummary WHERE daysdiff  gt  120   I get      invalid column name daysdiff       Maxlogtm is a datetime field   It s the little stuff that drives me crazy

User · Answer

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  gt  120

User · Answer

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  gt  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

User · Answer

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  gt  120

User · Answer

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  gt  0  Cheers  Kel

User · Answer

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      gt  120

User · Answer

SELECT    logcount  logUserID  maxlogtm     DATEDIFF day  maxlogtm  GETDATE    AS daysdiff FROM statslogsummary WHERE   DATEDIFF day  maxlogtm  GETDATE    gt  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  gt  120   Or see Adam s answer for a CTE version of the same

User · Answer

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  gt  120

User · Answer

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  gt  120

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005