Developer Forum

I have a query where I have a complex date expression as one of the
columns.

SELECT
date_column + INTERVAL( complex_jimmy_jam ) DAY AS
complex_date_calculation
FROM table

I want to refer to that column in the where clause

WHERE complex_date_calculation < NOW()

But mysql chokes on it.

1054: Unknown column 'complex_date_calculation' in 'where clause'

One way to do this is to wrap it in a sub-select

SELECT * FROM (
SELECT
date_column + INTERVAL( complex_jimmy_jam ) DAY AS
complex_date_calculation
FROM table
) AS alias
WHERE complex_date_calculation < NOW()

Is there a simpler way to express this?

I could also re-do the calculation in the WHERE clause, but that seems
dumb. Why have the database calculate that date twice? Or, will the
optimizer store that value?

> date_column + INTERVAL( complex_jimmy_jam ) DAY AS> > complex_date_calculation> > FROM table> >> > I want to refer to that column in the where clause> >> > WHERE complex_date_calculatio< < NOW()> >> > But mysql chokes on it.> >> > 1054: Unknown column 'complex_date_calculation' in 'where clause'> >> > One way to do this is to wrap it in a sub-select> >> > SELECT * FROM (> > SELE>T
> date_column + INTERVAL( complex_jimmy_jam ) >AY AS
> complex_date_calcu>ation
> FR>M table
> ) >S alias
> WHERE complex_date_calcu<ation>< >OW()
>
> Is there a simpler way to expre>s >his?
>
> I could also re-do the calculation in the WHERE clause, but th>t seems
> dumb. Why have the database calculate that date twice? Or, >ill the
> optimizer store that value?

Use the HAVING clause.

Awesome, Captain! Thanks :)