March 27, 2007 at 8:27 am
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!
March 27, 2007 at 8:40 am
First off you are probably getting due to the fact tblMain.colNumber is a numeric or integer data type where you comparison value is a character type thus a type mismatch will occurr and you get an error.
I think what you actually want is something like this
AND
(
(
(CASE WHEN @MaxRevs = '' THEN 1 ELSE 0 END) = 0 AND
tblMain.colNumber IN (SELECT colNumber FROM tblMain)) OR (
(CASE WHEN @MaxRevs != '' THEN 1 ELSE 0 END) = 0 AND
tblMain.colNumber IN (Select max(colNumber) From tblMain Group By [colParentNumber])))
March 27, 2007 at 8:50 am
Hi Antares686,
The column colNumber is actually a string since the user can define whatever format they need to be able to recognize it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply