Problem when using HAVING with aliases in MySQL

Question

I have a MySQL database with a table containing a list of dates. I need to find the first and last days in a gap in that table. They are the days off certain employee had in the last 30 days. I am using:

Select DATE, LEAD(DATE,1) over(ORDER BY DATE DESC) AS PREVDATE, datediff(DATE,LEAD(DATE,1) over(ORDER BY DATE DESC)) AS GAP from DAYSWORKED DATE>date_sub(CURRENT_DATE, interval 30 DAY) having GAP>1;

If I run the code without the HAVING clause, I get a list of dates and previous dates and the points where GAP>1 are the points where I have the days off, but hwen I include the clause HAVING to filter those points it doesn't work.

What am I doing wrong?

Thanks for your help !!!

it doesn't work ... what is the exact error message you are getting? Your query is missing a WHERE clause, but maybe this is just a copying error. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 25, 2021 at 6:13
please show your actual query and explain what you mean by "it doesn't work"; what does happen? please also edit your question to show output (as text, not an image) of show create table DAYSWORKED and some insert statements for sample data and desired and actual results for that sample data that demonstrate the problem — ysth
– ysth, Commented Jun 25, 2021 at 6:13
GAP column contains window function which is not allowed in HAVING clause, even implicitly. Because it affects the output rowset and hence it affects window function (self) result which is obtained between HAVING and ORDER BY. — Akina
– Akina, Commented Jun 25, 2021 at 6:31

Tim Biegeleisen · Accepted Answer · 2021-06-25 06:07:49Z

2

You'll need to subquery to access the GAP alias. Also, you need a proper WHERE clause, which begins with the word "where."

WITH cte AS ( SELECT DATE, LEAD(DATE) OVER (ORDER BY DATE DESC) AS PREVDATE, DATEDIFF(DATE, LEAD(DATE) OVER (ORDER BY DATE DESC)) AS GAP FROM DAYSWORKED WHERE DATE > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) ) SELECT * FROM cte WHERE GAP > 1;

answered Jun 25, 2021 at 6:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ysth Over a year ago

you can use aliases from a having clause. they've got something else going wrong

Tim Biegeleisen Over a year ago

@ysth I think you're right. I have asked the OP for clarification. My answer should still be valid at least, but I'll delete upon seeing more information.

Tim Biegeleisen Over a year ago

I'm wondering now if aliases which include analytic function calls can in fact be used in the HAVING clause.

Collectives™ on Stack Overflow

Problem when using HAVING with aliases in MySQL

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related