May 5, 2010 at 6:37 am
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.
Also, I try to wrap the code between the "OR" in () but they disappear if I save or run the query?
Bob
May 5, 2010 at 7:36 am
based on what you said and based on your where clause are you intending all the statements in between the OR's to be one group?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 5, 2010 at 8:02 am
If you read the previous post before I edited it I apologize I miss read your where clause to start with.
I had to copy and past it out to a query analyzer so I could read it. Your where clause actually looks good to me. so you where saying it works with the in if you comment out the OR sections?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 5, 2010 at 9:11 am
My report works correctly if I comment out the WHERE code starting at the first OR. Actually it works fine if I leave in any of the blocks of code between the "OR"s. That is, I can click Preview, then select multiple values from my dropdown list, and then the report returns only those rows that meet the search criteria. I know this is confusing. But if you look at my WHERE statement and use only one of the chunks of code between the "OR"s, it works. But if I try to use two or more chunks of this code seperated by "OR", it fails with a syntax error saying invalid near "," which I assume is in my IN.
Bob
May 5, 2010 at 9:19 am
I just realized where the error is.
I am not sure how the second group or the third group could possibly work on thee own.
In the secon group you have (@Service = '<No Selection>')
In the third section you have (@WoundClass = '<No Selection>')
I am not sure what you are looking to accomplish by these two lines but this is I am sure where the problem is. when the parameter is passed in you end up with.
[a,b,c]= '<No Selection>' in these two where clauses you are using the parameter as the column name.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 5, 2010 at 11:00 am
Well, this code works:
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 (@WoundClass = '<No Selection>') AND (@SurgeonName = '<No Selection>')
The reason I have things like @WoundClass = '<No Selection>' is because this is the default value of the dropdownlist for each of these parameters. There is no actual value in the database. I use this because the user does not have use all of the selection criteria. This is why I have all the various "OR" conditions in my code.
May 5, 2010 at 11:15 am
I am not saying you are wrong but the fundementals of SQL dictate that it is looking for Columnname=criteria. I have never seen any variation of that. Having the parameter before the = is using the value of parameter as the column name.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
December 13, 2010 at 1:09 pm
Which parameter(s) did you switch to multi-value?
Multi-value parameters get magically replaced with comma separated lists when more than one value is selected, for use with IN. E.g. a varchar @WoundClass with "Marmot" and "Self-inflicted" selected would expand to "'Marmot','Self-inflicted'".
"IN ('Marmot','Self-inflicted')" then works, but "'Marmot','Self-inflicted' = '<No Selection>'" is what gives these "incorrect syntax near ','" errors.
December 14, 2010 at 7:58 am
Your problem may be this bit of the where clause
(@Service = '<No Selection>')
Now that @Service is multi-value, I think you will need to do something different here.
(sorry can't work out how to get the < and > right above.)
December 14, 2010 at 8:17 am
You could always just try distinct Union statements for each OR section since you say each section works. Just a thought.
December 14, 2010 at 12:02 pm
If I understand what you are trying to do correctly, you want to create a report with three required parameter values (@StartDate, @EndDate, @SurgeonName) and two optional parameter values (@Service, @WoundClass). If that's so, this will work:
WHERE surgeries.sur_or_start_datetime BETWEEN CONVERT(datetime, @StartDate, 120) AND CONVERT(datetime, @EndDate, 120) + 1) AND
(@Service = '<No Selection>' OR surgeries.sur_hospital_service IN (@Service)) AND
(@WoundClass = '<No Selection>' OR surgeries.sur_wound_class = @WoundClass)) AND
(surgeries.sur_primary_surgeon_dr_name = @SurgeonName)
The construction of this WHERE clause requires that each of four conditions separated by the ANDs (date between @StartDate and @EndDate, evaluation of @Service, evaluation of @WoundClass, and evaluation of @SurgeonName) evaluate to TRUE. When a user does not select a value for @Service and/or @WoundClass, the nested OR clauses force those conditions to evaluate to TRUE when the report passes the default value of '<No Selection>'. When SQL Server is evaluating an OR clause, it stops when it reaches the first condition that evaluates to TRUE (no need to evaluate any other conditions because one TRUE in an OR clause makes the whole clause TRUE). For example, if the report passes '<No Selection>' as the value of @Service, then @Service='<No Selection>' will evaluate to TRUE and the condition (@Service = '<No Selection>' OR surgeries.sur_hospital_service IN (@Service)) will therefore evaluate to TRUE. If @Service has a value other than '<No Selection>', the condition @Service = '<No Selection>' will evaluate to FALSE and SQL Server will proceed to evaluate surgeries.sur_hospital_service IN (@Service).
One caveat - SSRS 2005 passes the values of a multi-value parameter as a comma-delimited string, e.g., 'EmergencyDept,OperatingRoom,OutpatientSuite'. SQL Server 2005 interprets this as a single string value, not an array of three values, so an IN clause will return TRUE only for a value of 'EmergencyDept,OperatingRoom,OutpatientSuite', not for values 'EmergencyDept','OperatingRoom', or 'OutpatientSuite'. There are plenty of workarounds for this problem posted on the web. Just search "SQL Server Reporting Services multivalue parameters IN clause." I understand that SSRS 2008 handles this natively and such workarounds are not required.
Good luck!
Jason Wolfkill
December 15, 2010 at 12:58 am
bobh0526 (5/5/2010)
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>')
Without getting into re-writing your whole WHERE clause, I think Nigel had it right, your
(@Service = '<No Selection>')
will mess you up for sure, so change that line to ('<No Selection>' IN (@Service)) The thing that you need to consider here is the user who selects '<No Selection>' and something else for the @Service parameter. Using the above line, if '<No Selection>' was one of the values selected, than it would trump all other selections. If that's not acceptable, let me know and I can give some other suggestions.
Also double check the datatype of your @Service parameter and make sure that is what it should be. If possible change you @StartDate and @EndDate to Datetime type parameters so you don't have to use the Convert function either.
If you do want to consider making your WHERE clause a bit more efficient, here is an alternative that I think would be helpful, you could tune this even more I'm sure, but this stays pretty simple. WHERE
(surgeries.sur_or_start_datetime BETWEEN CONVERT(datetime, @StartDate, 120) AND CONVERT(datetime, @EndDate, 120) + 1)
AND (surgeries.sur_primary_surgeon_dr_name = @SurgeonName)
AND (('<No Selection>' = @WoundClass)
OR (surgeries.sur_wound_class = @WoundClass))
AND (('<No Selection>' IN (@Service))
OR (surgeries.sur_hospital_service IN (@Service)))
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply