An expression of non-boolean type specified in a c

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

An expression of non-boolean type specified in a c

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-09-15 : 15:52:03

I am getting the error message, "An expression of non-boolean type specified in a context where a condition is expected, near ',' "
when running an ssrs 2008 2012 report.
The sql embedded in the dataset is:
IF @reportID <> 0
BEGIN
SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue
UNION
SELECT 'Students report 2', 2
UNION
SELECT 'Students report 3', 3
UNION
SELECT 'Students report 4', 4
UNION
SELECT 'Students report 5', 5
ORDER BY selectRptName
END
)

The sql runs fine in managment studio when I declare @reportID.

The sql runs fine with I comment out 'IF @reportID <> 0'.

The @reportID is a parmeter value that is passed to the applicable dataset.
The @reportID can have more than one value.

Thus can you show me sql and/or tell me what I need to do to solve the issue for me?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-15 : 16:44:41

This won't work if @reportID has more than one value. You'll get something like this passed to SQL.

If 1,2 <> 0
...
Which is of course not valid SQL ans will result in the very error you are seeing. You should try

If 0 not in (@reportID)
...

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-09-15 : 18:35:31

Thus I am thinking of calling the following following function to split out the parameter values:

FUNCTION [dbo].[fn_splitString]
(
@listString VARCHAR(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id
FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l
CROSS JOIN dbo.sequenceNumbers sn
WHERE sn.Num < LEN(l.listString)
AND SUBSTRING(l.listString, sn.Num, 1) = ','
)

GO

Can you show me sql code on how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString
function?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-16 : 07:32:12

You Dont need the function. In SSRs code the query as IN with your var in parents. At runtime, SSRs will substitute with either a single value or a list. Both should work just fine

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-09-16 : 10:48:26

solved with IN with your var in parents

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources