Case trouble with IN subquery

Search: Find

Home

Book

Contents

Preview

Articles

Tools

Newsletters

Premium

Ask

About

Most Popular Articles

Using Secure Sockets Layer (SSL) for SQL Server 2000 Reporting Services
Creating an SSL Certificate to ensure your confidential reports remain confidential

SQL Reporting Services - Answers to your questions

Notify me of new comments

Author

Thread: Case trouble with IN subquery

bubberz

Case trouble with IN subquery
Posted: Tuesday, March 27, 2007 3:28 PM (GST)

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!

Comments:

Author Thread:

JSelburg

Case trouble with IN subquery
Posted: Friday, April 20, 2007 11:54 PM (GST)

Withouth the rest of your WHERE clause, this is only a stab at a solution.

but use/try this instead.

WHERE (@MaxRevs = tblMain.colNumber OR @MaxRevs = 0)

This assumes your @MaxRevs will have a value of 0 if the user wants all values.

So another question would be, what does @MaxRevs contain when they want to view all numbers?