zero for Null

Question

zero for Null

sharonsql2013

SSCertifiable

Points: 5908
More actions
March 14, 2014 at 10:20 am

#284592

I am trying to pull data for a report.
Select * from Production
where DeviceName = 'Keyboard'
and Owner = 'Mark'
However I will declare owner as a parameter, However , If I pass Mark as an owner and there are no records for Device_Name Keyboard , I get blank table. Thus , my report causes the tablix to disappear if no row appears. I want it show '0' instead. Can I tweek the query to do so.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

Not sure if this is the real code or not, but I would recommend using the complete column list instead of select *. What if someone adds a new column how will your report handle an extra column?

You can use a case statement to handle the option for 0 for non-Keyboard devices, like so:Select Columnlist

,case when DeviceName = 'Keyboard' then DeviceName else '0' end as DeviceName

from Production

where Owner = 'Mark'

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

sharonsql2013

SSCertifiable

Points: 5908

March 14, 2014 at 12:30 pm

#1697246

Sure will try that.

djj SSCoach Points: 18831 More actions · Answer 3

Might also try:

Select * from Production

where ISNULL(DeviceName,'Keyboard') = 'Keyboard'

and Owner = 'Mark'

however, this does not use an index for DeviceName.

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

djj (3/17/2014)
Might also try:
Select * from Production
where ISNULL(DeviceName,'Keyboard') = 'Keyboard'
and Owner = 'Mark'
however, this does not use an index for DeviceName.

While this approach is a little easier to understand than Keith's it is also nonSARGable which means you have a query that will not be able to use any indexing on DeviceName. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/