asked on

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.

Jim Horn

Line 18 --> IN (@Resource)))
Looks like you have one too many right parens, or are missing a left paren somewhere.

Jim Horn

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.

nishant joshi

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

ASKER CERTIFIED SOLUTION

ValentinoV

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FMabey

ASKER

Excellent help with very good, clear article.