Re: float storage

On Jun 7, 7:56 pm, Wazusa Blong <fitzjarr...@xxxxxxx> wrote:
On Jun 7, 10:05 am, Jeff Kish <jeff.k...@xxxxxxx> wrote:

<snip>
I did notice that if I converted to number and then tried to
convert to float, that I got this message:

ORA-01440: column to be modified must be empty to decrease precision or scale

which I guess implies that number has more capacity than float.
Jeff Kish

Define 'capacity'. By default declaring a column as NUMBER generates
a NUMBER(38) definition; there is no scale (decimal) to such a
declaration. To increase the scale one must decrease the precision.

Not really. NUMBER without precision is not equivalent to
NUMBER(38,0). See below.

A NUMBER(38) can also be specified as NUMBER(38,0); to provide greater
scale one need define the column as NUMBER(X,Y) where Y is some non-
zero value less than or equal to X. Valid declarations would be:




NUMBER(38,9)
NUMBER(10,9)
NUMBER(4,3)
NUMBER(4,4)

Invalid specifications would be:

NUMBER(4,5)

or any declaration where Y was greater than X. The table will create
however the column can never be loaded due to:

ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this
column


Again, not quite true. You can easily load into such column if you
stuff in the right numbers. See below.

NUMBER() can be declared with or without a scale, as illustrated. The
column being 'moved' from NUMBER to FLOAT requires that the precision
be reduced to allow for additional scale; that the existing data may
meet the maximum precision declared for the current definition is why
you can't simply perform an ALTER TABLE to enact this change on a
populated column.

The documentation explains this.

David Fitzjarrell

Here's a simple demo of various NUMBER specifications:

SQL> create table numberz (maxfloat number, maxint number(38),
smallfloat number(4,5));

Table created.

SQL> desc numberz
Name Null? Type
----------------------------------------- --------
----------------------------
MAXFLOAT NUMBER
MAXINT NUMBER(38)
SMALLFLOAT NUMBER(4,5)


Note that MAXFLOAT and MAXINT differ in Type. To illustrate the point:

SQL> create table numberz2( n1 number(38), n2 number(38,0));

Table created.

SQL> desc numberz2
Name Null? Type
----------------------------------------- --------
----------------------------
N1 NUMBER(38)
N2 NUMBER(38)

Now these two are obviously equivalent...

SQL> drop table numberz2;

Table dropped.

Back to our NUMBERZ. Let's insert some numbers into each column and
see how the specifications affect their storage:

SQL> insert into numberz values(1234567890.1234567890,0.012345,
0.012345);

1 row created.

SQL> col maxfloat format 9999999999.9999999999
SQL> col maxint format 9999999999.9999999999
SQL> col smallfloat format 9999999999.9999999999
SQL> select * from numberz;

MAXFLOAT MAXINT SMALLFLOAT
---------------------- ---------------------- ----------------------
1234567890.1234567890 .0000000000 .0123500000

Hey, no ORA-1438 for that odd NUMBER(4,5) column! Now, let's see what
the different specifications did to our numbers... NUMBER preserved
our original floating-point number exactly; NUMBER(38) rounded
0.012345 to a nearest integer, which happens to be 0; and NUMBER(4,5)
rounded 0.012345 to 0.01235. This last fact needs to be explained a
bit better, and Oracle's very own documentation does it pretty good:

---
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, then Oracle returns an
error message. If the value exceeds the scale, then 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.
---

Indeed, there rarely is a need for numbers with scale greater than
precision, however they are not at all illegal and may have their use
cases.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

.

Relevant Pages

  • Re: syntax to add decimal column to access table using SQL?
    ... set the data type in SQL to numeric and the precision value to 10 and then ... the scale value to 3 ...
    (microsoft.public.access.gettingstarted)
  • Re: new here, my lang project... PT2
    ... The scale is limited by the resolution of ... I see this as a precision vs. accuracy issue. ... > physics core ... > just I am not sure how necessary the partitioning is at present. ...
    (comp.object)
  • Re: float storage and usage
    ... offers the maximum scale and precision.. ... float offers with widest ranges. ... Putting a float into a primary key is definitely not recommendable. ...
    (comp.databases.ms-sqlserver)
  • Re: decimal precision
    ... > not verify the Precision or Scale of Decimal parameter values. ... > So, when you create Command, which updates database from the dataset, you ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: float storage
    ... information scale and precision I can't determine in advance (customer data ... can vary wildly) so I wanted to use the datatype that offers the maximum scale ... I used the oracle 9i docs to drive my decision. ...
    (comp.databases.oracle.misc)