Select Case problem...

[scole954387]

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

[zac.carey]

sc*********@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

[S. Cole]

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

za*******@gmail.com wrote:

sc*********@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