?
Solved

An expression of non boolean type!!!

Posted on 2015-01-20
5
Medium Priority
?
113 Views
Last Modified: 2015-01-28
Hi all,

I have an SSRS project that has a dataset which includes the following where clause:

WHERE 
((@Resource IN ('Newhouse Section Fit out') and dbo.OPERATION_SCHED.RESOURCE_ID IN ('NE_BLAST',
'NE_PT_METAL',
'NE_PT_COAT1',
'NE_PT_COAT4',
'NE_PT_COAT2',
'NE_PT_COAT3',
'NE_PT_INSP',
'NE_FO_LANE'))
OR 
(@Resource IN ('Newhouse TNT Fit out') and dbo.OPERATION_SCHED.RESOURCE_ID IN ('NE_BLAST02',
'NE_PT_COAT1A',
'NE_PT_COAT2A',
'NE_PT_COAT3A',
'NE_PT_INSP_FET',
'NE_FO_LANE02'))
OR 
(@Resource NOT IN ('Newhouse Section Fit out','Newhouse TNT Fit out')  AND dbo.OPERATION_SCHED.RESOURCE_ID IN (@Resource)))
AND (dbo.OPERATION_SCHED.START_DATE >= @StartDate) AND 
                      (dbo.OPERATION_SCHED.START_DATE <= @EndDate) AND (dbo.OPERATION_SCHED.SCHEDULE_ID = @ScheduleID)
ORDER BY dbo.OPERATION_SCHED.START_DATE

Open in new window


@Resource is a parameter that allows multiple selection from a predefined dropdown.
@ScheduleID is a parameter that allows single selection from a predefined dropdown.
@StartDate & @EndDate are parameters that receive their value from calendar selections.

I believe the issue I have surrounds the @Resource parameter in this where clause... The problem is, I can't work out what it is!

I get an error as follows:

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

This error only occurs when I select more than one option from the dropdown.

Can anyone point me in the right direction?

Thanks in advance.
0
Comment
Question by:FMabey
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40560152
Line 18 --> IN (@Resource)))
Looks like you have one too many right parens, or are missing a left paren somewhere.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40560164
Disregard the above.

>@Resource is a parameter that allows multiple selection from a predefined dropdown.
So ... you are expecting to pass a single comma-delimeted parameter such as 'CA, NY, FL', and insert it into a query.  Won't work that way.

I'll ping SQL Server expert ValentinoV and have him comment here, as he's dealt with this situation before in SSRS, and I can't find the article he wrote on it.
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 40560210
Hi Fmabey,

Here isssue with query not with ssrs report.

Check this first query:

SELECT 1
WHERE ('a') IN ('a','b','c')

Open in new window


In above query consider 'a' as resource and query works well.But in below query resource value is ('a','b') - multiple value for this case query will fail.

SELECT 1
WHERE ('a','b') IN ('a','b','c')

Open in new window


Error with query would be
An expression of non-boolean type specified in a context where a condition is expected, near ','.

To avoid this can you can use UNION ALL to fix problem.


Thanks,
Nishant
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 40560375
I believe Jim is referring to the following article: Reporting On Data From Stored Procedures (part 2)

I know it might be a lot to go through but as you're using multi-valued parameters I believe it's worth the effort :)

Let me know if you have any further questions on that!
0
 
LVL 3

Author Closing Comment

by:FMabey
ID: 40574864
Excellent help with very good, clear article.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

809 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