CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
user990016
user990016

Reputation: 3378

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.

Upvotes: 264

Views: 337416

Answers (10)

Roman Samarsky
Roman Samarsky

Reputation: 390

Use the HAVING clause because the WHERE keyword cannot be used with aggregate functions.

SELECT
    logcount,
    logUserID,
    maxlogtm,
    DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
HAVING daysdiff > 120

Upvotes: 0

Michael Henry
Michael Henry

Reputation: 722

For me, the simplest way to use an ALIAS in the WHERE clause is to create a sub-query 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 FROM Q1 WHERE name_length > 0

Upvotes: 2

Scy
Scy

Reputation: 11

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

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)

Upvotes: 6

roier.rdz
roier.rdz

Reputation: 123

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.

Upvotes: 8

Pascal
Pascal

Reputation: 2405

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

Upvotes: 21

Jamie F
Jamie F

Reputation: 23789

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.

Upvotes: 296

Shekhar Joshi
Shekhar Joshi

Reputation: 1008

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

Upvotes: 9

roman
roman

Reputation: 117380

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

Upvotes: 11

Adam Wenger
Adam Wenger

Reputation: 17540

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

Upvotes: 100

Related Questions