Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

zero for Null Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 10:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 215, Visits: 639
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.
Post #1551240
Posted Friday, March 14, 2014 10:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 574, Visits: 866
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
Post #1551246
Posted Friday, March 14, 2014 12:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 215, Visits: 639
Sure will try that.
Post #1551285
Posted Monday, March 17, 2014 5:27 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 196, Visits: 719
Might also try:
Select * from Production 
where ISNULL(DeviceName,'Keyboard') = 'Keyboard'
and Owner = 'Mark'

however, this does not use an index for DeviceName.
Post #1551686
Posted Monday, March 17, 2014 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 13,134, Visits: 11,971
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1551730
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse