Re: float storage

On Jun 7, 7:56 pm, Wazusa Blong <fitzjarr..._at_cox.net> wrote:
> On Jun 7, 10:05 am, Jeff Kish <jeff.k..._at_mro.com> 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