Trouble with Case statement for subquery IN

[codecomm]

I have this in the SQL Server part too, but wasn't sure if it's more of a SSRS thing, or a T-SQL deal.

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.

The following doesn't seem to help me either in the SQL portion:
IF @MaxRevs = '' SELECT ....
ELSE
SELECT ....

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!

 

[mrdenny]

You will probably want to change to something like this.

AND ((@MaxRevs = '' AND tblMain.colNumber IN (SELECT ColNumber FROM tblMain)) OR (tblMain.ColNumber = (SELECT MAX(colNumber) FROM tblMain GROUP BY colParentNumber)))

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com