469,572 Members | 1,306 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

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

Jul 8 '06 #1
2 5003

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

Jul 8 '06 #2
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
Jul 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by Newbie | last post: by
3 posts views Thread by Tcs | last post: by
3 posts views Thread by mark.irwin | last post: by
8 posts views Thread by | last post: by
1 post views Thread by microsoft.public.dotnet.languages.vb | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.