Solved

An expression of non boolean type!!!

Posted on 2015-01-20
5
103 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
[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
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 500 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

728 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