?
Solved

non-boolean type specified in a context where a condition is expected

Posted on 2014-11-12
8
Medium Priority
?
2,034 Views
Last Modified: 2014-11-13
I have a query in SSRS and I get this error:

An expression of non-boolean type specified in a context where a condition is expected, near ','.

Here is my where clause that is causing the error:

WHERE (InsuredName1 in(@ClientName) or @ClientName IS NULL)
AND (ClaimNumber = @ClaimNumber or @ClaimNumber IS NULL)
0
Comment
Question by:Scott Williams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:Scott Williams
ID: 40438391
I can run the query inside SSRS query designer and it works without error.  
What could be causing this when I run the report?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40438398
Post the entire SQL statement.

>WHERE (InsuredName1 in(@ClientName)
Let me guess ... is @ClientName a series of comma-separated names, like 'Bob', 'Frank', 'Nancy'?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40438399
I would try to put that code inside a SQL Stored Procedure and call it with @ClientName varchar, @ClaimNumber as parameters in SSRS report instead of just running the query in SSRS
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Scott Williams
ID: 40438422
ClientName is a Multi Value Parameter that uses a query to push the values into a dropdown list,
0
 

Author Comment

by:Scott Williams
ID: 40438428
lcohan - can you show me how to do that?  My Query is attached.  

my query is attached
SELECT 
c.LossDate,
co.CorrespondenceID,
c.ClaimNumber,
co.[FileName],
co.Title,
co.EntryDate,
U1.LastName+', '+U1.FirstName as EnteredByName,
U2.LastName+', '+U2.FirstName ExaminerName,
AddressBook.LastFirstName as ClaimantName,
i.InsuredID,
InsuredName1

FROM Correspondence co (NOLOCK)
JOIN Claimant cl (NOLOCK) ON co.ClaimantID = cl.ClaimantID AND co.FileName like '%' + @Phrase + '%'
JOIN Claim c (NOLOCK) ON cl.ClaimID = c.ClaimID AND c.LossDate between @FromDate and @ToDate
JOIN Policy p (NOLOCK) ON c.PolicyID = p.PolicyID
JOIN Insured i (NOLOCK) ON p.InsuredID = i.InsuredID
LEFT OUTER JOIN CorrespondenceStatus cs (NOLOCK) ON co.StatusID = cs.StatusID
LEFT OUTER JOIN Users U1 (NOLOCK) ON co.EnteredBy = U1.UserName
LEFT OUTER JOIN Users U2 (NOLOCK) ON c.ExaminerCode = U2.UserName
LEFT OUTER JOIN AddressBook on cl.AddressBookID = AddressBook.AddressBookID

WHERE (InsuredName1 in(@ClientName) or @ClientName IS NULL)
AND (ClaimNumber = @ClaimNumber or @ClaimNumber IS NULL)

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40438442
Yeah that's not going to work, as IN(@parameter) expects a single value and not multiple comma-separated values.    I know this was asked and answered a couple of days ago, and I sent a message to the experts I believe answered it.

Good luck.
0
 

Author Comment

by:Scott Williams
ID: 40438563
Jim, what's not going to work?  The @ClientName parameter is a drop down box.  If I use it like this:
     Where InsuredName1 IN(@ClientName)
it works fine.  It's the adding of the Claim Number parameter that is causing my issue.  
What do you suggest?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 40439320
All: please know that "WHERE SomeField IN (@MultivaluedParam)" will work perfectly fine when the query is located inside the dataset in the report.  It will not work as expected when this code is moved to a stored proc.  In that case you'd need to use an additional function to split the string.  More details on how that would work can be found here: Reporting On Data From Stored Procedures (part 2)

Scott: I think you should get rid of this part: " or @ClientName IS NULL".  A multi-value parameter cannot be set to NULL so that condition would never occur.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question