Solved

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

Posted on 2014-11-12
8
1,765 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
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 65

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 39

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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 65

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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now