Solved

An expression of non boolean type!!!

Posted on 2015-01-20
5
95 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 65

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 65

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now