CODEWITHSUNDEEP
sql
Mike
Mike

Reputation: 67

How to Convert 'zero' as numeric value in CASE statement

I am trying to convert zero in the case statement for the [Per Occ Limit Between] to be a numeric value and then add along with the [SIR Per Occ], when the below condition satisfies. The code below works fine, but I am guessing it is considering the value 0 as string and doesn't perform the addition correctly. How do I cast this correctly, in order for the case condition to take 0 as the numeric value.?? I am using SQL server

Any help will be much appreciated! Thanks in Advance. 

     cast([SIR Per Occ] as float) * 1000000 as [SIR Per Occ],
     cast([SIR Agg] as float) * 1000000 as [SIR Agg],
     case when cast([Per Occ Limit Between] as float) is null
       then 0
       else
       cast([Per Occ Limit Between] as float) * 1000000 
       end as [Per Occ Limit Between],
     cast((cast([SIR Per Occ] as float)) + (cast([Per Occ Limit Between] as float)) as float) * 1000000 as [Policy Occ Attachment],

Upvotes: 1

Views: 58

Answers (1)

SqlZim
SqlZim

Reputation: 38033

Try this instead:

  [SIR Per Occ] = cast([SIR Per Occ] as float) * 1000000 
, [SIR Agg]     = cast([SIR Agg] as float) * 1000000 
, [Per Occ Limit Between] = coalesce(cast([Per Occ Limit Between] as float),0) * 1000000 
, [Policy Occ Attachment] = (cast([SIR Per Occ] as float) 
                          + coalesce(cast([Per Occ Limit Between] as float),0)) * 1000000

Upvotes: 2

Related Questions