Solved

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

Posted on 2014-11-12
8
1,932 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 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 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS -- hover over value OR column in grid report to display vaule 12 42
SSRS troubles 4 100
SSRS 2013 - Overlapping reports 2 47
SSRS - Powershell 7 73
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…
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Suggested Courses

738 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