WHERE and 'AS' problem

SELECT * , DATE_ADD( test_equipment_cal_date, INTERVAL
test_equipment_cal_days DAY ) AS test_equipment_expire_date
FROM `test_equipment`
WHERE test_equipment_expire_date < '2008-01-31'

#1054 - Unknown column 'test_equipment_expire_date' in 'where clause'

so, i simply can't do that ???
how am i supposed to do such a thing ?

On 31 Jan, 10:29, Geoff <foooooo...@hotmail.com> wrote:
> SELECT * , DATE_ADD( test_equipment_cal_date, INTERVAL
> test_equipment_cal_days DAY ) AS test_equipment_expire_date
> FROM `test_equipment`
> WHERE test_equipment_expire_date < '2008-01-31'
>
> #1054 - Unknown column 'test_equipment_expire_date' in 'where clause'
>
> so, i simply can't do that ???
> how am i supposed to do such a thing ?

Put it in the HAVING clause instead

Captain Paralytic wrote:
> On 31 Jan, 10:29, Geoff <foooooo...@hotmail.com> wrote:
>> SELECT * , DATE_ADD( test_equipment_cal_date, INTERVAL
>> test_equipment_cal_days DAY ) AS test_equipment_expire_date
>> FROM `test_equipment`
>> WHERE test_equipment_expire_date < '2008-01-31'
>>
>> #1054 - Unknown column 'test_equipment_expire_date' in 'where clause'
>>
>> so, i simply can't do that ???
>> how am i supposed to do such a thing ?
>
> Put it in the HAVING clause instead

nice one :)

was the first thing that came to mind
i though you used that with group by only though

got it working :)

On 31 Jan, 11:13, Geoff <foooooo...@hotmail.com> wrote:
> Captain Paralytic wrote:
> > On 31 Jan, 10:29, Geoff <foooooo...@hotmail.com> wrote:
> >> SELECT * , DATE_ADD( test_equipment_cal_date, INTERVAL
> >> test_equipment_cal_days DAY ) AS test_equipment_expire_date
> >> FROM `test_equipment`
> >> WHERE test_equipment_expire_date < '2008-01-31'
>
> >> #1054 - Unknown column 'test_equipment_expire_date' in 'where clause'
>
> >> so, i simply can't do that ???
> >> how am i supposed to do such a thing ?
>
> > Put it in the HAVING clause instead
>
> nice one :)
>
> was the first thing that came to mind
> i though you used that with group by only though
>
> got it working :)
There's a paragraph about MySQL's particular implementation of HAVING
on this page:

http://dev.mysql.com/doc/refman/5.0/en/select.html

Geoff wrote:
> Captain Paralytic wrote:
>> On 31 Jan, 10:29, Geoff <foooooo...@hotmail.com> wrote:
>>> SELECT * , DATE_ADD( test_equipment_cal_date, INTERVAL
>>> test_equipment_cal_days DAY ) AS test_equipment_expire_date
>>> FROM `test_equipment`
>>> WHERE test_equipment_expire_date < '2008-01-31'
>>>
>>> #1054 - Unknown column 'test_equipment_expire_date' in 'where clause'
>>>
>>> so, i simply can't do that ???
>>> how am i supposed to do such a thing ?
>>
>> Put it in the HAVING clause instead
>
> nice one :)
>
> was the first thing that came to mind
> i though you used that with group by only though
>
> got it working :)
>

That is true in ANSI SQL. MySQL extends that functionality.

Personally, I wish ANSI would pick up on MySQL's way of doing things. I
see no logical reason for the restriction, and the syntax can be very handy.

Otherwise you have to repeat the entire calculated expression in the
WHERE clause. A good optimizer should recognize that, but who knows?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

"Jerry Stuckle" <jstucklex@attglobal.net> schreef in bericht
news:eOednZcnMt7jWDzanZ2dnUVZ_ournZ2d@comcast.com. ..
> Geoff wrote:
>> Captain Paralytic wrote:
>>> On 31 Jan, 10:29, Geoff <foooooo...@hotmail.com> wrote:
>>>> SELECT * , DATE_ADD( test_equipment_cal_date, INTERVAL
>>>> test_equipment_cal_days DAY ) AS test_equipment_expire_date
>>>> FROM `test_equipment`
>>>> WHERE test_equipment_expire_date < '2008-01-31'
>>>>
>>>> #1054 - Unknown column 'test_equipment_expire_date' in 'where clause'
>>>>
>>>> so, i simply can't do that ???
>>>> how am i supposed to do such a thing ?
>>>
>>> Put it in the HAVING clause instead
>>
>> nice one :)
>>
>> was the first thing that came to mind
>> i though you used that with group by only though
>>
>> got it working :)
>>
>
> That is true in ANSI SQL. MySQL extends that functionality.
>
> Personally, I wish ANSI would pick up on MySQL's way of doing things. I
> see no logical reason for the restriction, and the syntax can be very
> handy.
>
> Otherwise you have to repeat the entire calculated expression in the WHERE
> clause. A good optimizer should recognize that, but who knows?
>

the disadvantage of some people writing

SELECT name FROM persons HAVING name='Santa';

will stop this from becoming ANSI, i guess.....