using conditional statements in where-clause

[pwiegers@gmail.com]

Hi,

I'm trying to use the result of a conditional statement in a where
clause, but i'm getting 1)nowhere 2) desperate :-)

The query is simple:
--------
SELECT
idUser,
(@ageraw:=YEAR( CURRENT_DATE()) - YEAR(dateofbirt h) -
(RIGHT(CURRENT_ DATE(),5)<'12-31')) AS temp,
@age:=if( @ageraw 100,0,
if( @ageraw < 10,0 ,
@ageraw
)) as age
from users u
---------

this works as a charm. But now I want to select users of a certain
age, and I add a where clause:
from users u having @age<50

This is not working. Nor is any other variant I can think of. How can
this be achieved?

greeting, and many thanks,

Paul

[Captain Paralytic]

Re: using conditional statements in where-clause



On 30 Jan, 13:56, "pwieg...@gmail .com" <pwieg...@gmail .comwrote:

Hi,
>
I'm trying to use the result of a conditional statement in a where
clause, but i'm getting 1)nowhere 2) desperate :-)
>
The query is simple:
--------
SELECT
idUser,
(@ageraw:=YEAR( CURRENT_DATE()) - YEAR(dateofbirt h) -
(RIGHT(CURRENT_ DATE(),5)<'12-31')) AS temp,
@age:=if( @ageraw 100,0,
if( @ageraw < 10,0 ,
@ageraw
)) as age
from users u
---------
>
this works as a charm. But now I want to select users of a certain
age, and I add a where clause:
from users u having @age<50
>
This is not working. Nor is any other variant I can think of. How can
this be achieved?
>
greeting, and many thanks,
>
Paul

I would have expected it to be
from users u having age<50

[pwiegers@gmail.com]

Re: using conditional statements in where-clause

>

I would have expected it to be
from users u having age<50

Me too.. but that does not work :-(
Any more idea's?

[Curtis]

Re: using conditional statements in where-clause

On Tue, 30 Jan 2007 12:56:13 -0800, pwiegers@gmail. com
<pwiegers@gmail .comwrote:

>>
>I would have expected it to be
>from users u having age<50

>
Me too.. but that does not work :-(
Any more idea's?
>

You'd probably get more responses from a SQL newsgroup. This doesn't seem
PHP-specific.

--
Curtis, http://dyersweb.com

[Tim Roberts]

Re: using conditional statements in where-clause

"Captain Paralytic" <paul_lautman@y ahoo.comwrote:

>
>
>On 30 Jan, 13:56, "pwieg...@gmail .com" <pwieg...@gmail .comwrote:

>Hi,
>>
>I'm trying to use the result of a conditional statement in a where
>clause, but i'm getting 1)nowhere 2) desperate :-)
>>
>The query is simple:
>--------
>SELECT
> idUser,
> (@ageraw:=YEAR( CURRENT_DATE()) - YEAR(dateofbirt h) -
>(RIGHT(CURRENT _DATE(),5)<'12-31')) AS temp,
>@age:=if( @ageraw 100,0,
> if( @ageraw < 10,0 ,
> @ageraw
> )) as age
>from users u
>---------
>>
>this works as a charm. But now I want to select users of a certain
>age, and I add a where clause:
> from users u having @age<50
>>
>This is not working. Nor is any other variant I can think of. How can
>this be achieved?
>>
>greeting, and many thanks,
>>
>Paul

>
>I would have expected it to be
>from users u having age<50

Ummm, why isn't it "FROM users u WHERE age<50"? Do you have a GROUP BY
clause that you didn't show us? HAVING is only used with GROUP BY.
--
Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

[Captain Paralytic]

Re: using conditional statements in where-clause

On 2 Feb, 06:40, Tim Roberts <t...@probo.com wrote:

"Captain Paralytic" <paul_laut...@y ahoo.comwrote:
>

On 30 Jan, 13:56, "pwieg...@gmail .com" <pwieg...@gmail .comwrote:

Hi,

>

I'm trying to use the result of a conditional statement in a where
clause, but i'm getting 1)nowhere 2) desperate :-)

>

The query is simple:
--------
SELECT
idUser,
(@ageraw:=YEAR( CURRENT_DATE()) - YEAR(dateofbirt h) -
(RIGHT(CURRENT_ DATE(),5)<'12-31')) AS temp,
@age:=if( @ageraw 100,0,
if( @ageraw < 10,0 ,
@ageraw
)) as age
from users u
---------

>

this works as a charm. But now I want to select users of a certain
age, and I add a where clause:
from users u having @age<50

>

This is not working. Nor is any other variant I can think of. How can
this be achieved?

>

greeting, and many thanks,

>

Paul

>

I would have expected it to be
from users u having age<50

>
Ummm, why isn't it "FROM users u WHERE age<50"? Do you have a GROUP BY
clause that you didn't show us? HAVING is only used with GROUP BY.
--
Tim Roberts, t...@probo.com
Providenza & Boekelheide, Inc.- Hide quoted text -
>
- Show quoted text -

Because you can't use column aliases in the WHERE clause but you can
in the HAVING clause.

>From the MySQL manual:

"A similar MySQL extension applies to the HAVING clause. The SQL
standard does not allow the HAVING clause to name any column that is
not found in the GROUP BY clause if it is not enclosed in an aggregate
function. MySQL allows the use of such columns to simplify
calculations."