OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Rouding issue (Numeric Data type rounding issue)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Rouding issue [message #274943]

Wed, 17 October 2007 16:39

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

Report message to a moderator

Re: Rouding issue [message #274960 is a reply to message #274943]

Wed, 17 October 2007 22:39

Barbara Boehmer
Messages: 9106
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.

Report message to a moderator

Re: Rouding issue [message #274996 is a reply to message #274943]

Thu, 18 October 2007 01:22

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Rouding issue [message #275066 is a reply to message #274996]

Thu, 18 October 2007 04:01

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.

Report message to a moderator

Re: Rouding issue [message #275089 is a reply to message #275066]

Thu, 18 October 2007 04:29

Littlefoot
Messages: 21825
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!

Report message to a moderator

Re: Rouding issue [message #275158 is a reply to message #274960]

Thu, 18 October 2007 10:41

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.

Report message to a moderator

Re: Rouding issue [message #275159 is a reply to message #275158]

Thu, 18 October 2007 10:49

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please post what you see as well as a test case that we can reproduce.

Regards
Michel

Report message to a moderator

Re: Rouding issue [message #275173 is a reply to message #275158]

Thu, 18 October 2007 12:26

joy_division
Messages: 4963
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.

Report message to a moderator

Re: Rouding issue [message #275184 is a reply to message #275173]

Thu, 18 October 2007 12:52

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

Report message to a moderator

Re: Rouding issue [message #275185 is a reply to message #275184]

Thu, 18 October 2007 12:59

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Rouding issue [message #275391 is a reply to message #275185]

Fri, 19 October 2007 13:48

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

Report message to a moderator

Previous Topic:	Oracle 10G Outer join problem. Need help asap
Next Topic:	which dbms_job is running

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 16 13:49:10 CDT 2025