Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pastie
Starting Member
1 Post |
 Posted - 2014-08-07 : 11:42:53
|
Hi there,I am new to the forums and to SQL to please be gentle! I am in the process of creating a report, with a multi value parameter which has 3 effects: -1) Select a single salesman displays all sales for the person selected2) Selecting 'Ignore' will ignore the parameter and display all sales, irrespective of salesperson. 3) Selecting multiple salesmen displays the results for multiple salespeople.At the moment I have 1 and 2 above working. I can see all sales, or sales for a single person. When I select 1, 2 or more salespeople I get the following error: -An Expression of non-boolean type specified in a context where a condition is expected near ','.Below is within my parameter SQL: -Union select '1','Ignore'And this is within my report SQL: -where data_policy.PolicyCSR IN (@CSR) Or (@CSR = '1'))For info, @CSR is my salesperson Parameter.I have seen this is a fairly common issue but I cant find a suitable fix. It will no doubt be my SQL inexperience but I am keen to learn!Many thanksGlenn |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-07 : 12:48:06
|
Let's assume that you selected two sales people, Joe and Jane. Then, after selection the parameter @CSR must contain:'Joe', 'Jane'Now, let's plug this into your WHERE clause and see what we get:where data_policy.PolicyCSR IN ('Joe', 'Jane') Or ('Joe', 'Jane' = '1'))I'm sure you can see that this produces invalid SQL. One thing you could try (untested):where data_policy.PolicyCSR IN ('1', @CSR) |
 |
|
|
|
|
|
|
|
An Expression of non-Boolean Specified....