case statement question 2

[rds80]

In the case statement below I expect the result to be blank when CM.Cost is NULL or = to .0000. But when CM.Cost = .0000, I get the value as 0%.

LA.Numerator isn't equal to 0, so that isnt the problem.

COALESCE(dbo.udfFormatPct(
            (SELECT CASE WHEN CM.Cost IS NULL OR CM.Cost = .0000 THEN ''
                 ELSE CONVERT(varchar, (LA.Numerator/CM.Cost) * 100) END 
             FROM tblControlMaster CM
             INNER JOIN @LoanAmount LA
                ON LA.ControlID = CM.ControlID
             WHERE LA.NoteID = N.NoteID), 0, 1), '')

Any suggestions? or more info I could provide.

Thanks.

 

[AlexCuse]

D'oh I was using varchar when I tested that last one :-(

Sorry RDS

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Where are you trying to display this data? Is it possible zeroes could be replaced on your front end? (Hint - the answer is yes ;-) ) That would probably be much less painful than throwing a million converts into your calculation, no?

Ignorance of certain subjects is a great part of wisdom

 

[rds80]

The data is being copied and pasted into excel on a weekly basis. Since it's a weekly basis, it's easier to copy and paste the data without doing a replace all and instead have these conversions.

Besides going thru this exercise I got to learn about why money is accurate to the thousandths, giving varchar a default value, the nullif, coalesce, and how to do simple verifications...Of course thanks to you guys.