asked on

SSRS problem with mutli-value param and "expression of non-boolean type specified ..."

Hi,

I'm getting an error of An expression of non-boolean type specified in a context where a condition is expected, near ','.

It's obviously to do with the multi-value param but I don't get why ?? it works with a single value but not multi ? Is it because the contractnumber is defined as int on the db ?

Here's the sql...

SELECT Count (job.scheduleddate) AS scheduled,
Count (job.datecompleted) AS completed
FROM job
INNER JOIN jobline
ON job.id = jobline.jobid
INNER JOIN site
ON job.siteid = site.id
INNER JOIN contract
ON site.contractid = contract.id
INNER JOIN lift
ON jobline.liftid = lift.id
INNER JOIN company
ON contract.companyid = company.id
INNER JOIN systemuser
ON job.engineerid = systemuser.id
WHERE ( job.lookupjobtypeid = 9 )
AND ( job.lookupjobstatusid <> 7 )
AND ( job.scheduleddate BETWEEN @pStartDate AND Dateadd(mi, 1439,
@pEndDate) )
AND ( lift.contractstatus = 1 )
AND ( jobline.lookupstatusid < 4 )
AND ( @pContract = -1
OR contract.contractnumber IN ( @pContract ) )
AND ( @pCompany = -1
OR company.id IN ( @pCompany ) )

PortletPaul

there is no obvious syntax error in the above, as you say it has to be the values help by a parameter, without knowing what you are placing in there it's impossible to be exact. However it is probably only going to be @pContract of @pCompany as these are intended to hold comma seperated lists. With contractnumber being an int you will not be able to simply send it a string containing what looks like a set of integers

'1,2,3,4,5' is still a string
It may be working as a single value because sql server is doing an implicit conversion to int for you; '1' could be converted to an int '1,2,3' could not. Hence the conversion is failing and the error message is indicating it cannot deal with any commas in that parameter.

you would have to either:
a. dynamically generated sql so the string is treated as integers when executed (not great)
b. convert contractnumber to varchar (also not terrific but better than a)
c. insert the wanted contractnumbers into a temp table? {+ edit or CTE which could be easier}

b. would be the easiest to implement but would adversely impact query efficiency - this impact may be acceptable - cant tell

d. ? a longer shot might be a function that returns a table if integers from a comma separated parameter (I've never done this, so its a maybe)

Steve Jebson

ASKER

Hi,

thanks for reply, apologies for late response (trip to Argos, bbq now built!), I agree with your analysis...it all works fine with single value in pContract (we'll ignore pCompany cos its the same thing, just a diff field). I must warn you though i'm pretty amateur when it somes to sql, and ssrs for that matter !...

b. if I understand you correctly this would be to change the definition on the Contract table to hold ContractNumber as varchar ? I can't change this, not my table and would be hard to justify.

c. sounds interesting, if I insert params into temp table would others running the rpt also pick these up ?? would I then need to delete them ? maybe clear it out before I insert ?....thinking while Im typing, could start to get a bit random !

if you could just expand a little on c. i'd like to give that a go...temp table or table var ? because this report is linked to 6 others via linked report i'd like to keep the temp data available across reports but not users if that makes sense ?

thanks

steve

PortletPaul

b. if I understand you correctly this would be to change the definition on the Contract table to hold ContractNumber as varchar ?
that's not what I had in mind, something a little easier (but not highly desirable)

AND ( @pContract = -1
OR cast(contract.contractnumber as varchar) IN ( @pContract ) )

I suggest you try this, it may not be too bad It would prove if the problem is those delimited strings. If the performance of this approach is acceptable then you may want to stick to it. The downside here is it would probably stop the use of the index on that field - which is why I say its not desirable - but many queries have to do similar things.

c. temp table could be tricky, yes it would need to be cleared, and it could be a problem for concurrent use. I mention it because it exists as an option, not sure I'd adopt it necessarily. A table var from a function might be the better variant. There are several scripts around to break-up delimited strings into pieces, it would be a small variation on one of these I think.

Using a recursive CTE might also work although not quite sure how to write that code.

Steve Jebson

ASKER

hi,

sadly the cast(contractnumber as varchar) didn't work, it still produced the same error

I've been trying to look at cte's....jeez, they're not so easy to understand.

i'm still a bit suprised that having a multi value integer as a parameter and not being able to do the simple where id in (@parameter) doesn't work ! maybe that's just my frustration coming through !

maybe the table var from function would be the easiest for me to research and find out how to do ?....

steve

PortletPaul

mmmm, if that cast to varchar didn't work, and before attempting more complexity, we have to consider that the analysis of the problem isn't correct. Precisely how are you forming the parameters? Perhaps you could inspect the actual values being passed this way?

SELECT Count(job.scheduleddate) AS scheduled
	, Count(job.datecompleted) AS completed
    , @pContract as pCntrct
    , @pCompany as pCmpy
FROM job
INNER JOIN jobline ON job.id = jobline.jobid
INNER JOIN site ON job.siteid = site.id
INNER JOIN contract ON site.contractid = contract.id
INNER JOIN lift ON jobline.liftid = lift.id
INNER JOIN company ON contract.companyid = company.id
INNER JOIN systemuser ON job.engineerid = systemuser.id
WHERE (job.lookupjobtypeid = 9)
	AND (job.lookupjobstatusid <> 7)
	AND (
		job.scheduleddate BETWEEN @pStartDate
			AND Dateadd(mi, 1439, @pEndDate)
		)
	AND (lift.contractstatus = 1)
	AND (jobline.lookupstatusid < 4)

Open in new window

PortletPaul

ok, thinking about this again, the error message you are getting (in initial question) may be due to a leading comma in your parameter
,3,4,5,6

but even if that leading comma is removed, the IN(@parameter) isn't going to work
and it isn't going to work even with cast(contractnumber to varchar)
(sorry, not a good suggestion)

please see this: http://sqlfiddle.com/#!3/1fa93/6934

Maybe others are able to offer a solution here

PortletPaul

The following approach uses 2 CTE's to split your parameters using an XML based approach. This assumes those parameters will only every carry digits and the delimiter (comma). i.e. if you put bad characters into the parameters the query would fail and don't use a delimiter that would "disturb XML" like a > or < (not that you are likely to).

You should also take care that your parameters should not start with a comma.

The third CTE here is purely so I could test easily, you would use real tables instead of this.

DECLARE @pContract varchar(max)
DECLARE @pCompany varchar(max)
declare @Splitter char(1)
SET @Splitter = ','

SET @pContract = '2,3,4,5'
SET @pCompany = '22,33,44'


;WITH
SplitContracts AS(
  SELECT CAST('<v>' + REPLACE(@pContract, @Splitter, '</v><v>') + '</v>' AS XML) AS Split
),
SplitCompanies AS(
  SELECT CAST('<v>' + REPLACE(@pCompany, @Splitter, '</v><v>') + '</v>' AS XML) AS Split
),
contract as (
  select 1 as contractnumber, 11 as company_id union all
  select 2 as contractnumber, 22 as company_id union all
  select 3 as contractnumber, 33 as company_id union all
  select 4 as contractnumber, 44 as company_id union all
  select 5 as contractnumber, 55 as company_id union all
  select 6 as contractnumber, 66 as company_id union all
  select 7 as contractnumber, 77 as company_id union all
  select 8 as contractnumber, 88 as company_id union all
  select 9 as contractnumber, 99
  )
select
*
from contract
where (@pContract = '-1'
       or
       contractnumber IN (
 
                            SELECT cast(x.v.value('.', 'bigint') as bigint) AS Value
                            FROM SplitContracts
                            CROSS APPLY Split.nodes('//v') x(v)
                         )
       )
AND   (@pCompany = '-1'
       or
       company_id IN (
 
                            SELECT cast(x.v.value('.', 'bigint') as bigint) AS Value
                            FROM SplitCompanies
                            CROSS APPLY Split.nodes('//v') x(v)
                         )
       )

Open in new window

also note that minus one in the above has changed to a string

PortletPaul

I think its also worth mentioning that this xml based approach is also based on the assumption you are not selecting thousands of contracts and/or companies, instead I hope you are likely to be selecting a relatively small number of these. Please test against the largest likely selections of these to ensure performance is acceptable.

These are useful references if you need more information or want to explore this parameter issue more deeply:

an often cited reference on this split string topic:
http://www.sommarskog.se/arrays-in-sql.html

useful introduction to the background of this issue, and some alternative methods for handling it
http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

Steve Jebson

ASKER

Hey PortletPaul,

First off, thankyou so much for putting so much effort into this, I really appreciate it, especially as it has been such a frustrating experience. I don't know about you but something doesn't feel quite right about this, so I decided to go back and start again and I've now identified what I think to be the problem although i'm not sure why !

It's to do with the @pContract = -1

and ( @pContract = -1 or c.ContractNumber in (@pContract ) ) doesn't work
and ( c.ContractNumber in (@pContract ) ) does work

any thoughts ??

steve

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

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

Start Free Trial

Steve Jebson

ASKER

It's all in the detail !! I completely missed it, thanks so much, really top notch support there, thanks again :D

Steve

SSRS problem with mutli-value param and &quot;expression of non-boolean type specified ...&quot;

SSRS problem with mutli-value param and "expression of non-boolean type specified ..."