Need to get only decimal values upto 2 digits if we have nothing in decimal points that has to be 0 [message #630738] |
Sun, 04 January 2015 23:55 |
|
suramsmanu
Messages: 1 Registered: January 2015 Location: United States
|
Junior Member |
|
|
Hi Folks,
can some one help me on this..
I need to Retrive a value from '-123.45' where my output shoud be like this
'-123.45'
If the value is like '-123'
.
I am using a query like this :
select LPAD (
CASE
WHEN ACC_NO = '71'
THEN
round(TO_NUMBER (
replace (replace (amount,'-',''),'.','') ),0) end ,8) ||
rpad(replace(substr(amount,instr(amount,'.')+1),'-',''),2,'0')
from dual;
any help on this...
Edited by Lalit : added code tags, please read the forum rules and abide by them in the future.
[Updated on: Mon, 05 January 2015 00:37] by Moderator Report message to a moderator
|
|
|
|
Re: Need to get only decimal values upto 2 digits if we have nothing in decimal points that has to be 0 [message #630740 is a reply to message #630739] |
Mon, 05 January 2015 00:25 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
SQL> WITH DATA AS(
2 SELECT '-123.45' num FROM dual UNION ALL
3 SELECT '123.45' num FROM dual UNION ALL
4 SELECT '-123' num FROM dual UNION ALL
5 SELECT '123' num FROM dual UNION ALL
6 SELECT '-0.45' num FROM dual
7 )
8 SELECT num,
9 CASE
10 WHEN TRUNC(num) = num
11 THEN '00'
12 WHEN num - TRUNC(num) <> 0
13 THEN substr(num, instr(num, '.', 1)+1)
14 END num_case
15 FROM DATA
16 /
NUM NUM_CAS
------- -------
-123.45 45
123.45 45
-123 00
123 00
-0.45 45
SQL>
1. What if the value is only in decimals? My solution gives output same for '-123.45' and '0.45'.
2. What if the values are positive? My solution gives same output for both negative and positive numbers.
Regards,
Lalit
|
|
|
|
|
|
|