Home » SQL & PL/SQL » SQL & PL/SQL » Display datatype double until 2 precision number (Oracle 10g)
Display datatype double until 2 precision number [message #375851] Mon, 15 December 2008 03:07 Go to next message
ednms
Messages: 39
Registered: November 2007
Member
Hi,

I have a table with datatype number(12,2).
If i want to insert data, i don't want the value to be rounded.
For example

Quote:
-3112.7 * 0.05


will return -155.635

I want the value to be inserted is -155.63 instead of -155.64.

To solve this, I used query as below

SELECT substr(-3112.7 * 0.05,1,instr(-3112.7 * 0.05,'.')+2) 
FROM dual;


But if i got value without decimal for my multiplication, it will return wrong value
Example

select substr(-5000 * 0.05,1,instr(-5000 * 0.05,'.')+2) 
from dual;


Any idea?
Thanks in advance.
Re: Display datatype double until 2 precision number [message #375852 is a reply to message #375851] Mon, 15 December 2008 03:10 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
store the result in varchar
yours
dr.s.raghunathan
Re: Display datatype double until 2 precision number [message #375854 is a reply to message #375851] Mon, 15 December 2008 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select round(-155.635+0.0001,2) from dual;
ROUND(-155.635+0.0001,2)
------------------------
                 -155.63

1 row selected.

Regards
Michel
Re: Display datatype double until 2 precision number [message #375855 is a reply to message #375854] Mon, 15 December 2008 03:17 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
michel,

i thought op wants the following too..

Quote:

i don't want the value to be rounded.



yours
dr.s.raghunathan
Re: Display datatype double until 2 precision number [message #375858 is a reply to message #375855] Mon, 15 December 2008 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OP should clearly explain what he wants.
Does he wants to be truncated? rounded but when last decimal is 5 then he wants it truncated? Other things?

Regards
Michel
Re: Display datatype double until 2 precision number [message #375865 is a reply to message #375851] Mon, 15 December 2008 03:26 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
Thanks,
Actualy the value is a variable

SELECT substr(&1 * 0.05,1,instr(&1 * 0.05,'.')+2)
FROM dual;


If i use
select round((&1*0.05)+0.0001,2) from dual;


&1 = -3112.75

it still round the value.

I need to use same sql for different type of value
Re: Display datatype double until 2 precision number [message #375867 is a reply to message #375865] Mon, 15 December 2008 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want? Explain it with words.

Regards
Michel
Re: Display datatype double until 2 precision number [message #375869 is a reply to message #375865] Mon, 15 December 2008 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select trunc(-3112.7 * 0.05,2) from dual;
TRUNC(-3112.7*0.05,2)
---------------------
              -155.63

1 row selected.

Regards
Michel
Re: Display datatype double until 2 precision number [message #375871 is a reply to message #375867] Mon, 15 December 2008 03:33 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
I want to store my multiplication until 2 precision(if any) without rounding the number.
Re: Display datatype double until 2 precision number [message #375873 is a reply to message #375869] Mon, 15 December 2008 03:35 Go to previous message
ednms
Messages: 39
Registered: November 2007
Member
Thanks.
I will use trunc.
Previous Topic: checking value of %rowtype
Next Topic: Commit in Procedure
Goto Forum:
  


Current Time: Wed Dec 07 18:41:25 CST 2016

Total time taken to generate the page: 0.09279 seconds