Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2113
  • Last Modified:

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)
0
Scott Williams
Asked:
Scott Williams
1 Solution
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now