Getting error Invalid column name days in sql server

Question

I am working on sql query, When i run the query i am getting error Invalid column name 'days'., Can anyone please help me, why i am getting error like this, it looks like there something issue with having, but still not able to resolve it, here is my query

SELECT
    COUNT(*) AS total
FROM
(
    SELECT *, ABS(DATEDIFF(day, GETDATE(), EpStart)) AS [days]
    FROM tb_Episode
    HAVING [days] <= ''
) AS episodes 
WHERE
    (episodes.EOEClaimDate is NULL or episodes.EOEClaimDate = '0000-00-00') AND
    episodes.PayerType = 'Ep' AND
    episodes.EpEnd < '2018-02-05' AND
    episodes.CustID = '27'

you cannot use the *alias* in the `having` clause. you must use the calculated form itself. eg :`abs(DATEDIFF(day, GETDATE(), EpStart)) <= ''` the column `days` can only be used in the outer query (outside subquery). You also need to use `WHERE` and not `HAVING` - `WHERE abs(DATEDIFF(day, GETDATE(), EpStart)) <= ''` — John Woo, Feb 05 '18 at 08:56
Scope issue. Move it to outside the sub-query: `WHERE [days] <= '' AND...` — jarlh, Feb 05 '18 at 08:56
Also, it doesn't make sense to compare a numerical quantity against empty string. What is your logic here? — Tim Biegeleisen, Feb 05 '18 at 08:56

Joe Taras · Accepted Answer · 2018-02-05T09:12:53.470

2

You can't use in HAVING clause the alias of your field.

Why you use HAVING instead of WHERE?

The same restriction for HAVING exists for WHERE,

So your query will become:

FROM
(
    SELECT *, ABS(DATEDIFF(day, GETDATE(), EpStart)) AS [days]
    FROM tb_Episode
    WHERE ABS(DATEDIFF(day, GETDATE(), EpStart)) <= ''
) AS episodes

edited Feb 05 '18 at 09:12

answered Feb 05 '18 at 08:57

Joe Taras

15,166
7
42
55

score 2 · Answer 2 · answered Feb 05 '18 at 09:02

According to this answer by @Codo you cannot using a HAVING on an alias/virtual column as each SQL clause is performed in a designated order

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

So because your column alias is defined after the HAVING clause you get the invalid column name error as the column doesn't technically exist yet.

Getting error Invalid column name days in sql server

2 Answers2