Home » SQL & PL/SQL » SQL & PL/SQL » Rouding issue (Numeric Data type rounding issue)
Rouding issue [message #274943] Wed, 17 October 2007 16:39 Go to next message
ocon
Messages: 4
Registered: October 2007
Location: LONG BEACH
Junior Member
Hi,

I have a table with a column data type Numeric (8,2).
When a value going into this column is higher than 10,000.00 when doing an insert the system automatically rounds the amount.
It seems that there is an implied rounding. This automatic rounding is causing a variance that I am trying to prevent. Can you please let me know how I can stop this rounding?

Thanks so much

Ocon
Re: Rouding issue [message #274960 is a reply to message #274943] Wed, 17 October 2007 22:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Change the 8 in your datatype to however big it needs to be or just use number.
Re: Rouding issue [message #274996 is a reply to message #274943] Thu, 18 October 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want a number of any precision but with 2 decimals use "number(*,2)":
SQL> create table t (col number(*,2));

Table created.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 COL                                       NUMBER(38,2)

Regards
Michel

Re: Rouding issue [message #275066 is a reply to message #274996] Thu, 18 October 2007 04:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thanks for that Michel - another Oracle feature I'd not seen before.
Re: Rouding issue [message #275089 is a reply to message #275066] Thu, 18 October 2007 04:29 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Recently there was a topic here where someone create a table using
SQL> CREATE TABLE brisime
  2    (id NUMBER(*),
  3     name VARCHAR2(20)
  4    );
My initial response was that such a code will NEVER be successfully executed on any Oracle database.

My next response was Embarassed and my ears were burning hot. Geee ... what an ignorant!
Re: Rouding issue [message #275158 is a reply to message #274960] Thu, 18 October 2007 10:41 Go to previous messageGo to next message
ocon
Messages: 4
Registered: October 2007
Location: LONG BEACH
Junior Member
Thank you for your input.

It continues to round even when I only use data type number without specifying any number for the precision. Would it be possible that is has to do with the settings of oracle database?

I welcome any other ideas that could be causing this unwanted rounding.

Thanks so much.
Re: Rouding issue [message #275159 is a reply to message #275158] Thu, 18 October 2007 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post what you see as well as a test case that we can reproduce.

Regards
Michel
Re: Rouding issue [message #275173 is a reply to message #275158] Thu, 18 October 2007 12:26 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
ocon wrote on Thu, 18 October 2007 11:41


It continues to round even when I only use data type number without specifying any number for the precision.



I tend to think you are either using some third party tool to view or you have an output mask on your column with the COLUMN clause.
Re: Rouding issue [message #275184 is a reply to message #275173] Thu, 18 October 2007 12:52 Go to previous messageGo to next message
ocon
Messages: 4
Registered: October 2007
Location: LONG BEACH
Junior Member
Yes,you are right. The insert is done by Oracle Warehouse Builder. The table column is defined as data type number (8,2).
In the mapping this field properties are:
Data Type - number
Precision - 8
Scale - 2
I do not where else to look? Any ideas?

Thanks
Re: Rouding issue [message #275185 is a reply to message #275184] Thu, 18 October 2007 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're still on number(8,2)? But why do we answer if you don't follow what we said?

Regards
Michel
Re: Rouding issue [message #275391 is a reply to message #275185] Fri, 19 October 2007 13:48 Go to previous message
ocon
Messages: 4
Registered: October 2007
Location: LONG BEACH
Junior Member
Thanks for your suggestions.

I found out that it had to do with the data. When data source is
a access file I have the rounding issue when I insert to the the table. When the data source is ASCII file then there is no problem.

I appreciate your help! Thanks
Previous Topic: Oracle 10G Outer join problem. Need help asap
Next Topic: which dbms_job is running
Goto Forum:
  


Current Time: Fri Dec 09 15:33:58 CST 2016

Total time taken to generate the page: 0.25445 seconds