mysql question : CASE

[Geradeaus]

I have searched but have not yet found how I can resolve the following
problem :

SELECT adId,
CASE
WHEN adId =1 THEN cityId
WHEN adId =2 THEN regionId
END AS extraField
FROM ad
WHERE extraField <> 0

The problem is "extraField " cannot be used in the WHERE clause... I am using
mySQL 4.1

Thanks in advance!

[Steve]

Re: mysql question : CASE

On Wed, 04 Jan 2006 17:05:27 +0100, Geradeaus wrote:
[color=blue]
> I have searched but have not yet found how I can resolve the following
> problem :
>
> SELECT adId,
> CASE
> WHEN adId =1 THEN cityId
> WHEN adId =2 THEN regionId
> END AS extraField
> FROM ad
> WHERE extraField <> 0
>
> The problem is "extraField " cannot be used in the WHERE clause... I am using
> mySQL 4.1
>
> Thanks in advance![/color]

Is there a union in mysql 4.1?

[maraguida@gmail.com]

Re: mysql question : CASE

You might want to try comp.databases. mysql for comprehensive answers.
(X-Post and Followup-To set)

Geradeaus wrote:[color=blue]
> I have searched but have not yet found how I can resolve the following
> problem :
>
> SELECT adId,
> CASE
> WHEN adId =1 THEN cityId
> WHEN adId =2 THEN regionId
> END AS extraField
> FROM ad
> WHERE extraField <> 0
>
> The problem is "extraField " cannot be used in the WHERE clause... I am using
> mySQL 4.1[/color]

....
WHERE adId in (1, 2)

not tested!

[Andy Hassall]

Re: mysql question : CASE

On Wed, 4 Jan 2006 17:05:27 +0100, "Geradeaus"
<gehegeradeaus_ nospam_@hotmail .com> wrote:
[color=blue]
>I have searched but have not yet found how I can resolve the following
>problem :
>
>SELECT adId,
> CASE
> WHEN adId =1 THEN cityId
> WHEN adId =2 THEN regionId
> END AS extraField
>FROM ad
>WHERE extraField <> 0
>
>The problem is "extraField " cannot be used in the WHERE clause... I am using
>mySQL 4.1[/color]

Repeat the CASE in the WHERE clause.

--
Andy Hassall :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Geradeaus]

Re: mysql question : CASE


"Andy Hassall" <andy@andyh.co. uk> schreef in bericht
news:tk2or1dp3l u97bnii8umplekt aplm8ovc9@4ax.c om...[color=blue]
> On Wed, 4 Jan 2006 17:05:27 +0100, "Geradeaus"
> <gehegeradeaus_ nospam_@hotmail .com> wrote:
>[color=green]
>>I have searched but have not yet found how I can resolve the following
>>problem :
>>
>>SELECT adId,
>> CASE
>> WHEN adId =1 THEN cityId
>> WHEN adId =2 THEN regionId
>> END AS extraField
>>FROM ad
>>WHERE extraField <> 0
>>
>>The problem is "extraField " cannot be used in the WHERE clause... I am
>>using
>>mySQL 4.1[/color]
>
> Repeat the CASE in the WHERE clause.
>
> --
> Andy Hassall :: andy@andyh.co.u k :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool[/color]

thanks when I repeat it, it works :)