May 5, 2010 at 5:36 am
I have a report with a dropdownlist as one of my parameters. I changed the parameter to be Mult-Value. When I click Preview, the dropdownlist does allow me to check more than one value. But I get an error saying there is incorrect syntax near the ",". I changed my query WHERE to use the IN () instead of the "=". I am not using stored procedures but just typed in my sql statement as text. Here is the WHERE statement:
WHERE (surgeries.sur_or_start_datetime BETWEEN CONVERT(datetime, @StartDate, 120) AND CONVERT(datetime, @EndDate, 120) + 1) AND
(surgeries.sur_hospital_service IN (@Service)) AND (surgeries.sur_wound_class = @WoundClass) AND
(surgeries.sur_primary_surgeon_dr_name = @SurgeonName)
OR
(surgeries.sur_or_start_datetime BETWEEN CONVERT(datetime, @StartDate, 120) AND CONVERT(datetime, @EndDate, 120) + 1) AND
(surgeries.sur_wound_class = @WoundClass) AND (surgeries.sur_primary_surgeon_dr_name = @SurgeonName) AND
(@Service = '<No Selection>')
OR
(surgeries.sur_or_start_datetime BETWEEN CONVERT(datetime, @StartDate, 120) AND CONVERT(datetime, @EndDate, 120) + 1) AND
(surgeries.sur_hospital_service IN (@Service)) AND (surgeries.sur_primary_surgeon_dr_name = @SurgeonName) AND
(@WoundClass = '<No Selection>')
As a test, I removed all the lines after the first "OR" and my report ran correctly. That is, I could multi-select services and they appeared on the report. I then tried each of chunks of SQL between each "OR" individually and they worked. But if I even try to use 2 chunks of this code seperated by the "OR" then I get that error. So it definitely has something to do when I add the OR. What am I doing wrong? I have posted this on several other forums and no one can figure it out.
Bob
May 5, 2010 at 5:50 am
Try placing brackets around the different logical parts of your OR statement.
Now you have:
WHERE something1 AND something2 ...
OR
somethinga AND somethingb ....
OR
...
Try the following:
WHERE (something1 AND something2 ...)
OR
(somethinga AND somethingb ....)
OR
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2010 at 5:54 am
Thanks for the reply. Sorry, but I forgot to mention something about that. I tried enclosing the code in parenthese (). But if I run the SQL statement or save it, they disappear. I am doing this on the Data tab. I think this may be the problem. Why won't SSRS allow me to use the ()?
Bob
May 5, 2010 at 6:05 am
I have no idea. I have never used SSRS before 🙂
I'm more of a SSIS/SSMS/SSAS user.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2010 at 6:35 am
Sorry, I probably posted this in the wrong forum.
May 5, 2010 at 7:12 am
No, I guess you didn't. SSRS is part of the Business Intelligence suite of SQL Server and since this is the forum Business Intelligence, you are probably correct of posting it here.
But I believe there are some dedicated SSRS forums on this site, with people who have far more knowledge about SSRS then I have.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply