decimal point manipulation

[Guest_imported]

Please see below : -

1> select reference, amount, decimals, "display"=
2> (case decimals
3> when 1 then (convert(numeric(5,1), amount / power (10,decimals)))
4> when 2 then (convert(numeric(4,2), amount / power (10,decimals)))
5> when 0 then (convert(numeric(6,0), amount / power (10,decimals)))
6> end)
7> from casetest
8> go

reference amount decimals display required
--------- ------ -------- -----------
ABCD 1234 0 1234.00 1234
EFGH 2345 1 234.00 234.5
IJKL 3456 2 34.00 34.56

How do I get my "required" values back ?
Thanks

 

[stoleru]

Hi!
It seems that convert function is your problem. I can only give you an Oracle example:
SELECT reference,amount,decimals,round(amount/power(10,decimals),decimals) "display" from casetest;

I hope this help you Eduard Stoleru
e_stoleru@yahoo.com

 

[karluk]

I think that the problem is you are doing the division before the convert. This makes the intermediate result an integer, and leads to the truncation you are seeing. Try something like

select reference, amount, decimals, "display"=
(case decimals
when 1 then (convert(numeric(5,1), convert(numeric(6,2), amount) / power (10,decimals)))
when 2 then (convert(numeric(4,2), convert(numeric(6,2), amount) / power (10,decimals)))
when 0 then (convert(numeric(6,0), convert(numeric(6,2), amount) / power (10,decimals)))
end)
from casetest
go