Home » SQL & PL/SQL » SQL & PL/SQL » to_number built in
to_number built in [message #288433] Mon, 17 December 2007 09:04 Go to next message
srinivas.k2005
Messages: 342
Registered: August 2006
Senior Member
Hi ,
SQL> select to_char(3.2,999.999) from dual;

TO_CHAR(
--------
   3.200

SQL> ed
Wrote file afiedt.buf

  1* select to_number(to_char(3.2,999.999),999.999) from dual
SQL> /

TO_NUMBER(TO_CHAR(3.2,999.999),999.999)
---------------------------------------
                                    3.2

But i want the output even after converting to number as 3.200

Can somebody help me.

Thanks,
Srinivas

[Updated on: Mon, 17 December 2007 13:08] by Moderator

Report message to a moderator

Re: to_number built in [message #288437 is a reply to message #288433] Mon, 17 December 2007 09:35 Go to previous messageGo to next message
kollanagoracle
Messages: 5
Registered: December 2007
Junior Member
Hi
AS per my knowledge there is no direct function to show precission values as zero . u can use rpad instead.
SELECT RPAD(TO_NUMBER(3.2),LENGTH(3.2)+3,0) FROM dual
(Rpad doesn't convert the value into char, still the value is in number format only)
if i find anything other than this i will let u know.

Thanks
Naga

Re: to_number built in [message #288477 is a reply to message #288433] Mon, 17 December 2007 13:06 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no purpose in doing that; 3.2 is simply 3.2, no matter how many zeros you add to the right from the decimal point. It is its representation to the end user that should matter, but that's the formatting problem! I believe that any tool you use has such a capability; Report builder does. Forms as well. MS Excel has it. Even SQL*Plus does:
SQL> set numformat 999G990D000
SQL> select 3.2, 0.25, 1234.221 from dual;

         3.2         0.25     1234.221
------------ ------------ ------------
       3.200        0.250    1,234.221
The right way to do that is to use the TO_CHAR function and apply the desired format. I'd suggest you to accept it as-it-is: compute with numbers, display as (formatted) numbers using the TO_CHAR.
Re: to_number built in [message #288489 is a reply to message #288433] Mon, 17 December 2007 14:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In addition to what's already said, I noticed something else in your post: the second parameter of to_char is supposed to be a string, not a number. You need to use quotes around the format.
(Not that that will help you in your actual question, but it is a fundamental error in the use of to_char)
Re: to_number built in [message #288493 is a reply to message #288489] Mon, 17 December 2007 15:09 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
We must be oldfashioned, Frank ...
SQL> select to_char(3.2, 999.999) From dual;

TO_CHAR(
--------
   3.200

SQL>
Re: to_number built in [message #288514 is a reply to message #288433] Mon, 17 December 2007 21:52 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
A little info on numbers:

http://www-eleves-isia.cma.fr/documentation/OracleDoc/NUMBER-DATATYPE.html

SCALE AND PRECISION 
    Specify the scale and precision of a number column for extra 
    integrity checking on input.  Specifying scale and precision does 
    not force all values to a fixed length.  If a value exceeds the 
    precision, Oracle returns an error.  If a value exceeds the scale, 
    Oracle rounds it.

NEGATIVE SCALE 
    If the scale is negative, the actual data is rounded to the 
    specified  number of places to the left of the decimal point. For 
    example, a specification of (10,-2) means to round to hundreds. 
 
SCALE GREATER THAN PRECISION 
    You can specify a scale that is greater than precision, although it 
    is uncommon.  In this case, the precision specifies the maximum 
    number of digits to the right of the decimal point.  As with all 
    number datatypes, if the value exceeds the precision, Oracle returns 
    an error.  If the value exceeds the scale, Oracle rounds the value. 
    For example, a column defined as NUMBER(4,5) requires a zero for the 
    first digit after the decimal point and rounds all values past the 
    fifth digit after the decimal point.  The following examples show 
    the effects of a scale greater than precision: 

There is some discussion about number storage in oracle and rouding practices etc. on ASKTOMHOME if you are interested real details. With out being technical on this one, a number internally has little to do with how it is displayed. At display time it is rounded/truncated/whatever based on display settings and masks you choose (or defaulted to for lack of a choice). When a number is passed around in code it is not subject to these rules, but rather to datatype conversion and calculation rules, and the ever present, possible rounding error.

clear columns

select 1/9*9 is_this_1 from dual;

So?...

select case when 1=1/9*9 then 1 else 0 end is_this_one from dual;

Hmmm...

Good luck, Kevin
Previous Topic: regional language
Next Topic: One record per id
Goto Forum:
  


Current Time: Sat Dec 10 05:26:20 CST 2016

Total time taken to generate the page: 0.10716 seconds