Select Case problem...

Hi,

I have a problem. I have written a SQL statement that has a nested
select case statement on the 'where' clause to condition the results.

--------------------------------------------------------
SELECT b.ISBN, b.Title, b.SmallImage, f.idForSale, count(f.idForSale)

FROM books b, forsale f

WHERE b.idBooks=f.Books_idBooks AND (select case when %s = 0 then
f.BookCondition_idConditions>0 ELSE f.BookCondition_idConditions=%s
end)

GROUP BY b.ISBN

--------------------------------------------------------

The statement works fine on my test server running Apache2, however
receives the following error on the production server running MySQL
4.0.25-standard-log.

Error:
You have an error in your SQL syntax. Check the manual that corresponds
to your MySQL server version for the right syntax to use near 'select
case when 1 = 0 then f.BookCondition_idConditions>0 ELSE

Same error received no matter what value %s gets (0,1,2, etc).

Anyone have any ideas why the statement doesn't work on my production
server? I've researched the mysql manual and select case appears to be
a valid function for this version of mysql.

Any help is appreciated

Thanks,
S. Cole

Re: Select Case problem...

scole954387@gmail.com wrote:
> Hi,
>
> I have a problem. I have written a SQL statement that has a nested
> select case statement on the 'where' clause to condition the results.
>
> --------------------------------------------------------
> SELECT b.ISBN, b.Title, b.SmallImage, f.idForSale, count(f.idForSale)
>
> FROM books b, forsale f
>
> WHERE b.idBooks=f.Books_idBooks AND (select case when %s = 0 then
> f.BookCondition_idConditions>0 ELSE f.BookCondition_idConditions=%s
> end)
>
> GROUP BY b.ISBN
>
> --------------------------------------------------------
>
> The statement works fine on my test server running Apache2, however
> receives the following error on the production server running MySQL
> 4.0.25-standard-log.
>
> Error:
> You have an error in your SQL syntax. Check the manual that corresponds
> to your MySQL server version for the right syntax to use near 'select
> case when 1 = 0 then f.BookCondition_idConditions>0 ELSE
>
> Same error received no matter what value %s gets (0,1,2, etc).
>
> Anyone have any ideas why the statement doesn't work on my production
> server? I've researched the mysql manual and select case appears to be
> a valid function for this version of mysql.
>
> Any help is appreciated
>
> Thanks,
> S. Cole

i don't think you can use subselects pre 4.1

Re: Select Case problem...

Thanks for the reply.

I got it to work by removing the () and the SELECT before the case...
so the sql that worked was:

------------------------------------
SELECT b.ISBN, b.Title, b.SmallImage, f.idForSale, count(f.idForSale)

FROM books b, forsale f

WHERE b.idBooks=f.Books_idBooks AND CASE WHEN %s = 0 THEN
f.BookCondition_idConditions>0 ELSE f.BookCondition_idConditions=%s END

GROUP BY b.ISBN
------------------------------------

Hope this helps someone else trying to figure out the same.

S. Cole

zac.carey@gmail.com wrote:
> scole954387@gmail.com wrote:
> > Hi,
> >
> > I have a problem. I have written a SQL statement that has a nested
> > select case statement on the 'where' clause to condition the results.
> >
> > --------------------------------------------------------
> > SELECT b.ISBN, b.Title, b.SmallImage, f.idForSale, count(f.idForSale)
> >
> > FROM books b, forsale f
> >
> > WHERE b.idBooks=f.Books_idBooks AND (select case when %s = 0 then
> > f.BookCondition_idConditions>0 ELSE f.BookCondition_idConditions=%s
> > end)
> >
> > GROUP BY b.ISBN
> >
> > --------------------------------------------------------
> >
> > The statement works fine on my test server running Apache2, however
> > receives the following error on the production server running MySQL
> > 4.0.25-standard-log.
> >
> > Error:
> > You have an error in your SQL syntax. Check the manual that corresponds
> > to your MySQL server version for the right syntax to use near 'select
> > case when 1 = 0 then f.BookCondition_idConditions>0 ELSE
> >
> > Same error received no matter what value %s gets (0,1,2, etc).
> >
> > Anyone have any ideas why the statement doesn't work on my production
> > server? I've researched the mysql manual and select case appears to be
> > a valid function for this version of mysql.
> >
> > Any help is appreciated
> >
> > Thanks,
> > S. Cole
>
> i don't think you can use subselects pre 4.1