Case trouble for IN subquery

• sol

  SSCrazy

  Points: 2294

  More actions

  March 27, 2007 at 8:27 am

  #176895

  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!

• Antares686

  SSC Guru

  Points: 125444

  More actions

  March 27, 2007 at 8:40 am

  #697166

  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])))

• sol

  SSCrazy

  Points: 2294

  More actions

  March 27, 2007 at 8:50 am

  #697172

  Hi Antares686,

  The column colNumber is actually a string since the user can define whatever format they need to be able to recognize it.