Solved

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

Posted on 2013-05-18
11
2,403 Views
Last Modified: 2013-05-21
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 ) )
0
Comment
Question by:stevejebson
  • 7
  • 4
11 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39177227
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)
0
 
LVL 1

Author Comment

by:stevejebson
ID: 39177539
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39177813
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.
0
 
LVL 1

Author Comment

by:stevejebson
ID: 39179488
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39179759
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

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39179811
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39180209
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39180221
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
0
 
LVL 1

Author Comment

by:stevejebson
ID: 39182160
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
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39182883
also note that minus one in the above has changed to a string
I mentioned this a while ago :)

and ( @pContract = '-1' --<< there needs to be quotes use here
or c.ContractNumber in (@pContract ) )
0
 
LVL 1

Author Comment

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

Steve
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

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

10 Experts available now in Live!

Get 1:1 Help Now