It works if I drop it in the case statement but I think I need it. Additional sample data below.

I want to exclude XYZ and include ABC replacing its NULL with 1

if OBJECT_ID('tempdb..#MyData') is not null

drop table #MyData

create table #MyData

(

SomeVal varchar(10),

SomeVal2 varchar(10)

)

insert #MyData

select '10', 'DEF' union all

select '125', 'HIJ' union all

select '-4.67625', 'LMN' union all

select NULL, 'ABC' union all

select NULL, 'XYZ'

select

*,

case SomeVal2 when 'abc' then 1 else CONVERT(decimal(20,6), SomeVal)END

from #MyData

WHERE

case SomeVal2 w