Precision of measurements: Soil Water Content

meh
----------
This is a larger issue that may relate to many tables in the database, although here we use the "SOIL_WATER_CONTENT" on SOILS_BEE.

I noticed values with 14 decimals for the Soils BEE water content experiment. THis sounds like too much, so after consultation with Diana,
I received that 3 digits after the comma is as far as the precision for the value goes.

On Toad/Oracle, a column whose Type is defined as "NUMBER" w/o any further details, it gets maximum precision, or about 38 digits after the comma or so.

Oracle lets you control that (I quote from help pages)

"You can specify a fixed-point number as NUMBER(p,s), where the following holds:

The argument p is the precision or the total number of significant decimal digits, where the most significant digit is the left-most nonzero digit and the least significant digit is the right-most known digit.

The argument s is the scale, or the number of digits from the decimal point to the least significant digit. The scale ranges from -84 to 127.

Positive scale is the number of significant digits to the right of the decimal point up to and including the least significant digit.

Negative scale is the number of significant digits to the left of the decimal point up to but not including the least significant digit. For negative scale, the least significant digit is on the left side of the decimal point, because the number is rounded to the specified number of places to the left of the decimal point."

And, more to the point: type NUMBER(4,5): insert .000127 into the column, the value is stored as .00013.
Other example:
Table USER1.NUMBERCOMBO:
Columns:
COL1 NUMBER
COL2 NUMBER (3)
COL3 NUMBER (6,2)
COL4 NUMBER (6,1)
COL5 NUMBER (6,-2)
-------------------- how does 123.89 fare for all this columns?
COL1: 123.89
COL2: 124
COL3: 123.89
COL4: 123.9
COL5: 100

SO if we alter the table with a NUMBER(6,4) type for the SOILS_BEE (generous, as the MAX SOIL WATER CONTENT is like less than 20.0),
then should do, even for future higher precision instrumentation.

HOWEVER, a table w/ data cannot be changed -- apparently we need to discard the data, change, re-enter data, which means, we need a mini-project here.

SQL> set define off
SQL> ALTER TABLE HOARE.SOILS_BEE
MODIFY(SOIL_WATER_CONTENT NUMBER(6,4))
ALTER TABLE HOARE.SOILS_BEE
MODIFY(SOIL_WATER_CONTENT NUMBER(6,4))
Error at line 3
ORA-01440: column to be modified must be empty to decrease precision or scale

Script Terminated on line 3.

but let's begin with an example

Status: 

Priority: 

Normal