CODEWITHSUNDEEP
sqlnetezza
user2740397
user2740397

Reputation: 31

how to get rid of decimal points in netezza

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.

Upvotes: 0

Views: 2296

Answers (2)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10073

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;

Upvotes: 1

N West
N West

Reputation: 6819

Have you tried putting single quotes around your format strings?

select to_char(amount,'99')

Upvotes: 0

Related Questions