how to get rid of decimal points in netezza

Question

Hi I have a weird requirement

if an amount value is 0.00 i need to display it as 0 and if its something else like 23.12 I need to have the decimal points and display as 23.12... tried below code in netezza but doesn't work

select 
case when amount=0.00 then 0
else amount
end;

select case when amount=0.00 then to_char(amount,99)
else to_char(amount,999999.99)
end;

they work when I write as select to_char(amount,99) from _v_dual; but doesn't work in case statement I get error like invalid format in to-char...

am completely stuck here any help is greatly appreciated.

Answer 1

This works for me in my Netezza db

select to_char(0.00,99) from _v_dual;
select 
case when amount=0.00 then 0
else amount
end
from
(select 0.00 as amount) a;

Answer 2

Have you tried putting single quotes around your format strings?

select to_char(amount,'99')