An expression of non boolean type!!!

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.
LVL 3
FMabeyAsked:
Who is Participating?
 
ValentinoVBI ConsultantCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Line 18 --> IN (@Resource)))
Looks like you have one too many right parens, or are missing a left paren somewhere.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
nishant joshiTechnology Development ConsultantCommented:
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
 
FMabeyAuthor Commented:
Excellent help with very good, clear article.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.