DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Number and precision

  1. #1
    Join Date
    Feb 2001
    Posts
    99
    okay, what am I missing here. I have a remote table that I am selecting data from, and then inserting into a local table.

    On the remote table, the columns is defined as number(10).
    on my end, the column is defined as number(10,2).

    The problme comes on the insertion. I select the remote field just fine. However, when I go to insert the data, I get the ora-01438 precision error for a certain amount.

    I they send me 500,000 it works fine. a check of the value before it comes to me is showing 50000000 as the number, and it gets displayed on my application as $500,000.

    However, if they send me a six figure dollar amount, I get the precision error. they tried to send me an amount of 1,000,000 and I got the error. The number was in the field as 100000000, which is 9 digits. However, an insertion error occurs (ora-01438). I know that rounding is going on, and thats the reason for the scale. As a test, they sent me 999,999.99 It was in the remote column (from a select) as 99999999. In my local column, it rounded it o 1000000.

    What am I missing here?

  2. #2
    Join Date
    Apr 2001
    Posts
    118
    You can not store a number with more than 8 digits to the left of the decimal in a column defined as NUMBER(10,2). That's why you are getting the ORA-01438 error when you tried to insert 100000000 for $1 million but did not get an error inserting 50000000 for $500 grand. I think you want to divide the value by 100 before inserting it into your local column.



  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    A number(10,2) can not hold a number as large as a number(10). With 10,2 you can store 8 non decimal digits with 2 decimal digits. If you want your target database to hold number(10) plus the two decimal places define your local column as number(12,2). You may want to change both columns to Number(12,2) which should eliminate the confusion.


  4. #4
    Join Date
    Feb 2001
    Posts
    99

    Talking

    Thanks all. Did some more testing. My confusion was that I thought 10 was total to left of decimal, not total overall.

    I did some more testing.
    1,000,000.01 went in fine as is.
    10,000,000.01 went in as 10,000,000 (since only 8 digits allowed, rounding occured.
    100,000,000.0 caused the precision error.

    Thanks for the responses. I can now write a trouble ticket for vendor to fix problem on both sides of our interface.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width