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

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)
Scott WilliamsData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott WilliamsData AnalystAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
lcohanDatabase AnalystCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Scott WilliamsData AnalystAuthor Commented:
ClientName is a Multi Value Parameter that uses a query to push the values into a dropdown list,
0
Scott WilliamsData AnalystAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Scott WilliamsData AnalystAuthor Commented:
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
ValentinoVBI ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.