When I run the following sql query statement it does not format as numeric(10,0)
but numeric(10,3).
If I replace cast(Oct as numeric(10,3)) with null it works.
select case when col='Gallons'
then cast(Oct as numeric(10,0))
else cast(Oct as numeric(10,3))
end as Oct
from (select 'Gallons' as col, 225.00 as Oct) a
Why would it behave in this manner?
A case expression returns a single type. It must decide between numeric(10, 0) and numeric(10, 3). The latter is the more general, so it will choose that.
If you want to control what the result set looks like, then convert the value to a string:
select (case when col = 'Gallons'
then cast(cast(Oct as numeric(10, 0)) as varchar(255))
else cast(cast(Oct as numeric(10, 3)) as varchar(255))
end) as Oct
from (select 'Gallons' as col, 225.00 as Oct) a
You can also do this using the str() function.
To keep both formats you can cast the numeric values to varchar:
select case when col='Gallons' then cast(cast(Oct as numeric(10,0)) as varchar(max))
else cast(cast(Oct as numeric(10,3)) as varchar(max)) end as Oct
from (select 'Gallons' as col, 225.00 as Oct union all select 'Other' as col, 123.45 as Oct) a
Result:
