Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A report currently computes a "totals" column like so:

,CASE WHEN ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) +
           ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0                 
      THEN ''
      ELSE CONVERT(VARCHAR(15), 
           ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) +
           ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0)) 
      END AS 'MealsTotal'

How can I get a blank cell when a total equals zero, instead of NULL or "0", and avoid doing the computation twice?

share|improve this question
add comment

1 Answer

up vote 8 down vote accepted

As a suggestion, don't use 'single quotes' for column aliases - it is not supported in certain scenarios, and makes it look like a string literal. If you need to quote column names (in this case you don't), use [square brackets]. Also try to use table aliases to improve readability. If you reference those tables 10 times, we don't really need to see CLM_BREAKFAST_TYPEA 10 times.

To be clear, the above is additional commentary meant to explain why the code sample below doesn't look exactly like yours. The following re-write of your expression does do exactly what your question asks.

,MealsTotal = COALESCE(NULLIF(CONVERT(VARCHAR(15),
       COALESCE(A.MealsA, 0) +
       COALESCE(B.MealsB, 0)),0),'')
FROM
... dbo.CLM_BREAKFAST_TYPEA AS A
... some kind of join
... dbo.CLM_BREAKFAST_TYPEB AS B

SQLfiddle demo

share|improve this answer
    
Thank you, Aaron; since this seems more like a comment than an answer, as time and motivation permits, would you please delete it and move it to a comment, above, or re-word as an answer? I appreciate the insights, but I'm struggling to see this as an answer to my question. Thank you. –  schultkl Aug 8 '13 at 3:11
2  
@schultkl huh? I added commentary in addition to re-writing your MealsTotal calculation. Did you try it before you down-voted it? Did you click on the SQLfiddle link I provided to further demonstrate that the way I wrote the expression does precisely what your question asks? Please try it before down-voting and suggesting that this answer should have been a comment, and maybe ask for clarification before down-voting the only person who has attempted to help you - that's not a great way to show appreciation. –  Aaron Bertrand Aug 8 '13 at 3:45
    
: o ) Thank you Aaron--to answer your questions: No; and no. –  schultkl Aug 8 '13 at 17:10
    
To follow-up: I'll remove the downvote--I do appreciate your help and clarification. When I saw the answer beginning with a discussion of table aliases (late last night), my eyes glazed over and I did not notice you had also supplied an actual answer to the question--I assumed the code only served as an example of your preferences on using table aliases. Looking at the edit history of the answer, I now see how you started with only the answer, then added the (helpful) notes above. Thank you again. –  schultkl Aug 8 '13 at 17:17
    
@schultkl I strongly recommend in the future that you read the entire answer before casting judgment. And remember that you are coming here for help, and people try to help you out of their own free time. Generally we don't care about reputation but punishing us because you didn't read or comprehend is, well, pretty crappy. –  Aaron Bertrand Aug 8 '13 at 17:35
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.