Skip to content
Advertisement

Replace 0 in one field in case statement result that is stringing fields

I have a statement like the following:

select distinct s.field1, s.field 2, 

Case when s.intfield3 = 2018 then (RTRIM(cast(s.intfield4 as char (2))) +'/1/'+ cast(s.intfield3 as char (4)))

when c.intfield3 = 2018 then (RTRIM(cast(c.intfield4 as char(2))) +'/1/'+ cast(c.intfield3 as char (4)))
End as newfield

My issue is s.intfield4 & c.intfield4 have some 0 values. I need to change those 0 values to a 12. I’ve tried a few things, but nothing is working.

Answer

you can use

case when x = 0 then 12 else x end