SQL Server Performance

Case trouble for IN subquery

Discussion in 'SQL Server Reporting Services' started by bubberz, Mar 27, 2007.

  1. bubberz New Member

    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!
  2. Adriaan New Member

    You're mixing CASE and IN, which just won't work.

    You also appear to think a SELECT statement will be recognized as such even if you put it between single quotes, iow making it a string literal, which again just won't work.

    I would create two separate queries, and use IF to execute either one:

    IF @MaxRevs = ''
    SELECT ...............
    ELSE
    SELECT ...............
  3. bubberz New Member

    Adriann,

    Thanks for the reply.

    I must have some syntax wrong since I get an error about an open paran.

    Here's my WHERE clause:

    AND
    CASE @MaxRevs WHEN '' THEN '(tblMain.colNumber IN
    (SELECT colNumber FROM tblMain))'
    ELSE '(tblMain.colNumber IN
    (SELECT MAX(colNumber) FROM tblMain GROUP BY [colParentNumber]))'
    END
  4. Adriaan New Member

    Like I said: it just won't work - iow it's invalid syntax.

    Don't try to do it all in just one query, write two queries and use control-of-flow language to execute either one.

    You can also let the client program decide which query to send through ADO.

    Better still, you should create a stored procedure that executes either query, based on the @MaxRevs parameter.
  5. bubberz New Member

    Adriaan,

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

    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.

    Thanks!

Share This Page