Case trouble for IN subquery | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Case trouble for IN subquery

I have the following in my WHERE clause for my Sql Server Reporting Services 2000 report (the user can use the parameter @MaxRevs to view all numbers (colNumber) or only the max of colNumbers when grouped by colParentNumber): AND (tblMain.colNumber IN CASE @MaxRevs WHEN ” THEN ‘(SELECT colNumber FROM tblMain)’ ELSE ‘(Select max(colNumber) From tblMain Group By [colParentNumber])’ END)) I get the following error:
1. ADO error: Syntax error or ADO access error I’ve used Case in the past, but it was for a "Like" portion in my WHERE clause. Thanks!
You’re mixing CASE and IN, which just won’t work. You also appear to think a SELECT statement will be recognized as such even if you put it between single quotes, iow making it a string literal, which again just won’t work. I would create two separate queries, and use IF to execute either one: IF @MaxRevs = ”
SELECT ……………
ELSE
SELECT ……………
Adriann, Thanks for the reply. I must have some syntax wrong since I get an error about an open paran. Here’s my WHERE clause: AND
CASE @MaxRevs WHEN ” THEN ‘(tblMain.colNumber IN
(SELECT colNumber FROM tblMain))’
ELSE ‘(tblMain.colNumber IN
(SELECT MAX(colNumber) FROM tblMain GROUP BY [colParentNumber]))’
END
Like I said: it just won’t work – iow it’s invalid syntax. Don’t try to do it all in just one query, write two queries and use control-of-flow language to execute either one. You can also let the client program decide which query to send through ADO. Better still, you should create a stored procedure that executes either query, based on the @MaxRevs parameter.
Adriaan, The following doesn’t seem to help me either in the SQL portion:
IF @MaxRevs = ” SELECT ….
ELSE
SELECT …. The report will say something to the affect that the visual designer can’t handle this SQL, and moves on w/o any further notices or errors. With the above query, nothing is ever returned. I can even name the parameter to @MaxRevs55, which doesn’t even exist, and the report just brings back a blank page w/o any errors. Thanks!
]]>