1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
<html>
<head>
<title>firstworks Programming with SQL Relay - Precision and Scale</title>
<link href="../css/styles.css" rel="stylesheet">
</head>
<body>
<span class="heading1">Precision and Scale</span><br><br>
<span class="heading1">Definitions</span><br><br>
<p>Precision and scale are important concepts to understand if you need to deal
with decimal numbers</p>
<dl>
<dt><b>precision</b></dt>
<dd>The total number of digits in a decimal number, both before and after the
decimal point.</dd>
<dt><b>scale</b></dt>
<dd>The total number of digits after the decimal point in a number.</dd>
</dl>
<p>Here are some examples:</p>
<table border="1">
<tr><th>Number</th><th>Precision</th><th>Scale</th></tr>
<tr><td>5.333</td><td>4</td><td>3</td></tr>
<tr><td>15.333</td><td>5</td><td>3</td></tr>
<tr><td>115.333</td><td>6</td><td>3</td></tr>
<tr><td>115.33</td><td>5</td><td>2</td></tr>
</table>
<span class="heading1">Columns With Precision And Scale</span><br><br>
<p>Many databases support numeric datatypes with precision and scale attribute.
For example, Oracle supports a "number" type. This query creates a table with
a column that can store numbers with precision of 5 and scale of 2. That is,
it can store numbers with up to 5 total digits, 3 of which are reserved for the
whole number component of the number and 2 of which are reserved for decimal
places.</p>
<blockquote>
<pre>
create table testtable (col1 number(5,2))
</pre>
</blockquote>
<p>Any number with 3 or fewer whole number digits and 2 or fewer decimal digits
may be inserted into this column.</p>
<blockquote>
<pre>
0> insert into testtable values (5.2);
0> insert into testtable values (5.22);
0> insert into testtable values (55.2);
0> insert into testtable values (55.22);
0> insert into testtable values (555.2);
0> insert into testtable values (555.22);
0> select * from testtable;
COL1
======
5.2
5.22
55.2
55.22
555.2
555.22
</pre>
</blockquote>
<p>Numbers with more than 2 decimal digits may be inserted into this column,
but they will be rounded up or down as appropriate.</p>
<blockquote>
<pre>
0> insert into testtable values (5.221);
0> insert into testtable values (5.225);
0> insert into testtable values (5.229);
0> select * from testtable;
COL1
====
5.22
5.23
5.23
</pre>
</blockquote>
<p>Since 2 digits are reserved for decimal digits. Numbers with more than 3
whole number digits may not be inserted into this column, even if the total
number of digits is less than 5. Attempts to do so will generate errors.</p>
<blockquote>
<pre>
0> insert into testtable values (5555.22);
ORA-01438: value larger than specified precision allows for this column
0> insert into testtable values (5555.2);
ORA-01438: value larger than specified precision allows for this column
0> insert into testtable values (5555);
ORA-01438: value larger than specified precision allows for this column
</pre>
</blockquote>
<span class="heading1">Arithmetic Involving Numbers With Different
Precision And Scale</span><br><br>
<p>When 2 columns with different precision and/or scale are involved in
arithmentic, the precision and scale of the result are not limited by the
precision and scale of the factors. The result will have whatever precision
and scale are necessary to represent the number.</p>
<p>For example, 9.2 has a precision of 2 and scale of 1 and 9.22 has a
precision of 3 and scale of 2. When added, the sum, 18.42, has a precision
of 4 and scale of 2. When multiplied, the product, 84.824, has a precision of
5 and scale of 3.</p>
<blockquote>
<pre>
0> select 9.2+9.22 from dual
9.2+9.22
=========
18.42
0> select 9.2*9.22 from dual
9.2*9.22
=========
84.824
</pre>
</blockquote>
<span class="heading1">Rounding Errors</span><br><br>
<p>As shown in the previous example, multiplication can result in numbers with
larger scale than either of the two numbers that were multiplied. If the
result is stored back into a column with smaller scale than is necessary to
represent the product, rounding will occur.</p>
<p>This can be a frustrating occurrance when dealing with money. It is common
to use columns with scale of 2 for money but such a column cannot be used
to keep track of fractions of cents. Only 2 decimal places are required when
monetary values which are added together or multiplied by whole numbers, but if
a monetary value is multiplied by a decimal number then it could require more
than 2 decimal places to store the result. Using only 2 decimal places will
introduce rounding errors quickly.</p>
<p>Since numbers cannot have infinite scale, it is always possible to introduce
rounding errors. You have to decide how much of a rounding error is acceptable
when designing your tables.</p>
<span class="heading1">Truncation</span><br><br>
<p>If a value is inserted back and forth between columns with different scales,
the original scale of the value will be lost and the value will be rounded and
truncated. For instance:</p>
<blockquote>
0> create table testtable1 (col1 number(3,2));
0> create table testtable2 (col1 number(2,1));
0> insert into testtable1 values (1.29);
0> insert into testtable2 select * from testtable1;
0> insert into testtable1 select * from testtable2;
0> select * from testtable1;
COL1
====
1.29
1.3
</blockquote>
<p>When 1.29 was inserted into col1 of testtable2, which can only accommodate a
single decimal digit, it was rounded up to 1.3 and the second decimal digit
was truncated. Now, col1 of testtable2 contains 1.3 rather than 1.29 and 1.3
is what gets selected from col1 of testtable2 and inserted back into testtable1
rather than 1.29.</p>
<span class="heading1">Bind and Substitution Variables</span><br><br>
<p>Computer hardware stores decimal numbers using as many decimal places as it
can (floating point format). But databases store decimal numbers using a
precise number of decimal places (fixed point format). So, when supplying a
floating point number as the value of a bind or substitution variable, you must
constrain the number of decimal places by supplying a precision and scale for
that number.</p>
<p>You should set the scale equal to the number of decimal places that you
believe your variable is accurate to, and the precision equal to the scale
plus the maximum number of whole number digits your variable could contain.
For instance, if you plan on storing values between 0 and 255, accurate to
3 decimal places, then you should use scale 3 and precision 6.</p>
<p>Note, however, that the scale of a bind variable will not always be
preserved. If you use a bind variable to insert a floating point number into a
column with smaller scale than you specified for your number, your number will
be rounded down upon insertion. For instance, if you pass in 1.2999 with scale
of 4 and precision of 5 into a column with scale of 1 and precision of 2, the
insert will succeed but your number will be rounded up to 1.3. Similarly, if
you use a bind variable to pass a floating point number into a comparison, it
will be converted to have the same scale as the value it is being compared to,
which could cause rounding to occur.</p>
</html>
|
