MySQL Pass max() column as interval days

Question

I have problem with passing MAX(t.to_days) as max_days to DATE_ADD(t.date, INTERVAL max_days DAY).

Query:

select t.*, MAX(t.to_days) as max_days
  from transactions t 
  where CURRENT_DATE > DATE_ADD(t.date, INTERVAL max_days  DAY)

returning error: Unknown column 'max_days' in 'where clause'.

I also tried:

select t.*, @num:=CAST(MAX(t.to_days) AS UNSIGNED) 
  from transactions t 
  where CURRENT_DATE > DATE_ADD(t.date, INTERVAL @num  DAY)

but DATE_ADD returning null.

Answer 1

There isn't any sample data and expect result, but I guess you can try to use HAVING instead of WHERE, because MAX is Aggregate function, and judgment can't use alias name.

select t.*, MAX(to_days) as max_days 
from transactions t 
HAVING CURRENT_DATE > DATE_ADD(`date`, INTERVAL MAX(to_days) DAY)

Here is my mock sample sqlfiddle