-1

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?

9

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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