Question regarding Select Case in query 1

[dianemarie]

Hello, we have quite a few reports where the selection criteria is determined by the user via a parameter. In an earlier post I was shown how to use the Select Case in the Where part of the query to reference the parameter. It works well. However, our reports are very flexible and we often have several selection criteria parameters. When I add the second Select Case statement, the query returns an error. What I am using is below, with the parameter being @Salesman, and the oh.salesman and oh.listdiv fields being chars. Does anyone see an obvious mistake? Also, if you create reports that allow users to manipulate the select criteria via a parameter, do you do it differently? Thanks for any help, I know I've been needy!

CASE @Salesman
When 'X24' then oh.salesman not in ('24','07')
When '24' then oh.salesman = '24'
When 'C' then oh.listdiv = 'C' else ' '
End) as Slsman

 

[andrea96]

What error are you getting?

 

[dianemarie]

Hi. I'm getting "query generated error. incorrect syntax near the keyword 'not'. incorrect syntax near the keyword 'and'. Microsoft SQL error 156."

I've tried commenting out various parts of the statement to pin it down but no matter what I leave or take out, I get some version of this error.

If it helps, my entire Where clause looks like:

SELECT

(CASE @CNX
WHEN 'Y' THEN oh.cnx ELSE ' '
END) as cnx,

(CASE @Salesman
When 'X24' then oh.salesman not in ('24','07')
When '24' then oh.salesman = '24'
When 'C' then oh.listdiv = 'C' else ' '
End) as Slsman,

oh.orders, oh.owner, oh.lnum, oh.listname, oh.runtype, oh.ordrdate, oh.maildate, oh.shipdate, oh.wantdate, oh.cnx, etc. etc. fields go on and on. Thanks again.

 

[andrea96]

To do the second part in the WHERE clause, I think you need to do nested CASE statements like this.

WHERE (CASE @Salesman 
         WHEN 'X24' THEN
             CASE oh.salesman 
                 WHEN '24' THEN 1
                 WHEN '07 THEN 1 
                 ELSE 0 
             END
         WHEN '24' THEN 
             CASE oh.salesman 
                 WHEN '24' THEN 1 
                 ELSE 0 
             END      
         WHEN 'C' THEN 
             CASE oh.listdiv 
                 WHEN 'C' THEN 1
                 ELSE 0
             END
         ELSE 0
    END) = 1

Basically, 1 is True and 0 is False. There may be errors in the code; I just typed it out quickly to show you how I would do it.

 

[dianemarie]

Hi Andrea, thanks so much. I got this to work for the most part with some tweaking. With the code below I know have a column called slsman, that returns a 1 or a 0 depending on the paramter criteria. All good. Now I want to drop records where slsman = 0, but this is where I'm struggling. I can't get the select case to accept the = 1 you put on the end of your code (incorrect syntax near '=' error). I also tried adding a Where slsman = 1, also tried where slsman <> 0, but I am getting an "invalid column name 'slsman' error. So I know have the records flagging properly with 1's and 0's but can't drop the 0's. Thanks again. Current code:

(CASE @Salesman
WHEN 'X24' THEN
CASE oh.salesman
WHEN '24' THEN 0
WHEN '07' THEN 0
ELSE 1
END
WHEN '24' THEN
CASE oh.salesman
WHEN '24' THEN 1
ELSE 0
END
WHEN 'C' THEN
CASE oh.listdiv
WHEN 'C' THEN 1
ELSE 0
END
ELSE ' '
END) as slsman,

 

[andrea96]

Are you putting the CASE before or after your WHERE? You can't name the column in your SELECT statement and then reference it in the WHERE clause. You will have to put the CASE after the WHERE without the "as slsman", then the "= 1" should work.

 

[dianemarie]

Well now... That is a beautiful thing. Thank you so much Andrea for all your help on this. Big learning curve for me (Crystal to SSRS) and the Tek-Tips contributors have been invaluable. I love you guys (and girls)!