Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bubberz
Constraint Violating Yak Guru
289 Posts |
 Posted - 2007-03-27 : 12:09:36
|
| 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 ....ELSESELECT ....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! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
bubberz
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-03-27 : 12:37:04
|
| Hello!I've posted this in both forums since I don't know if it's a T-SQL or a SSRS issue.The following doesn't seem to help me either in the SQL portion of my report:IF @MaxRevs = '' SELECT ....ELSESELECT ....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. |
 |
|
|
Dance Doll
Yak Posting Veteran
54 Posts |
Posted - 2007-04-23 : 13:22:36
|
| Case statements expect to return only one record. You will get syntax error by using IN with Case directly.Here is a link to do alternative CASE statement and get multiple records.http://www.databasejournal.com/features/mssql/article.php/3666131 |
|
|
|
|
|
|
Case trouble for IN subquery