refer to complex expression value in where clause

refer to complex expression value in where clause

Post by steve lefe » Sat, 27 Feb 2010 00:35:01


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?
 
 
 

refer to complex expression value in where clause

Post by Captain Pa » Sat, 27 Feb 2010 01:09:21


> 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.

 
 
 

refer to complex expression value in where clause

Post by steve lefe » Sat, 27 Feb 2010 02:15:26


Awesome, Captain! Thanks :)