Home » SQL & PL/SQL » SQL & PL/SQL » Need to get only decimal values upto 2 digits if we have nothing in decimal points that has to be 0 (oracle 11g R2)
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 Go to next message
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'


Out put 

45


If the value is like '-123'

output should be '00'
.


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 #630739 is a reply to message #630738] Mon, 05 January 2015 00:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
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 Go to previous messageGo to next message
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
Re: Need to get only decimal values upto 2 digits if we have nothing in decimal points that has to be 0 [message #630749 is a reply to message #630738] Mon, 05 January 2015 01:55 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Is this a college homework question? If so, I think the idea will be to use the various mathematical functions, not string manipulation functions.
Consider using ABS and ROUND. Try them individually, and then with a bit of arithmetic.
Re: Need to get only decimal values upto 2 digits if we have nothing in decimal points that has to be 0 [message #631016 is a reply to message #630749] Wed, 07 January 2015 22:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
If output needs insignificant digits then it must be a string formatting problem at some point.

But point is well taken. If this is a homework assignment, we don't do those. You need to read up on math functions to get the remainder you need and the use string functions to format the result.

Good luck. Kevin
Re: Need to get only decimal values upto 2 digits if we have nothing in decimal points that has to be 0 [message #631031 is a reply to message #631016] Thu, 08 January 2015 01:41 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Subtract the ROUND from the ABS (which, in English only, sounds like an advert for a slimming technique).
Re: Need to get only decimal values upto 2 digits if we have nothing in decimal points that has to be 0 [message #631035 is a reply to message #631031] Thu, 08 January 2015 02:37 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Laughing
Previous Topic: creation of view by passing the values dynamically
Next Topic: different outputs on same parameter (datatype date) while using 'like' and '='
Goto Forum:
  


Current Time: Fri Apr 19 06:18:41 CDT 2024