×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 28 May 2012 11:45, Robert Rogerson wrote:
<<SNIP>>
When I change the definition of shippedUnits to 31p 0 (the max from
the manual) there is no warning. But when I change the definition to
11p 0 (what I thought should be the min) I get the warning.
Use a scalar cast function to express the desired attributes of the
summation. For example, instead of "sum(dhqtss * dhpack)", request
"decimal(sum(dhqtss * dhpack), 11, 0)" or "cast(sum(dhqtss * dhpack) as
decimal(11, 0))"
I was calculating the precision as follows. <<SNIP>>
The SUM function is then used which is understand to be basically
addition.
(From the SQL reference)
<snip>Addition and subtraction
The scale of the result of addition and subtraction
is max (s,s'). The precision is
min(mp,max(p-s,p'-s')+max(s,s')+1).
</snip>
So I would think the precision is 10 + 1 or 11 which leads to 11p 0.
I think it's the SUM function that I'm not calculating correctly.
This is my thought process but obviously it is incorrect. Can someone
please point out where I'm going wrong so I don't make this error
again in the future.
SUM is an aggregate of potentially many values; i.e. the addition of
zero to many values. The precision\scale rules for "addition" apply to
an operand on either side of the addition operator. In the given
scenario, the _maximum precision_ (mp) is the default precision for the
SUM aggregate [presumably as defaulted from the DECRESULT() parameter on
the CRTSQLxxx]:
_i Aggregate functions i_
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstcolfunc.htm
" ...
_SUM_
...
The SUM function returns the sum of a set of numbers.
numeric-expression
The argument values must be any built-in numeric data type.
The data type of the result is the same as the data type of the argument
values except that the result is:
* A double-precision floating point if the argument values are
single-precision floating point
* A large integer if the argument values are small integers
* A decimal with precision mp and scale s if the argument values
are decimal or nonzero scale binary numbers with precision p and scale s.
For information on the values of p, s, and mp, see Decimal arithmetic in
SQL. <link below>
...
"
_i Expressions i_
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstch2expr.htm#decarithmetic
" ...
_Decimal arithmetic in SQL_
The following formulas define the precision and scale of the result of
decimal operations in SQL. The symbols p and s denote the precision and
scale of the first operand and the symbols p' and s' denote the
precision and scale of the second operand.
The symbol mp denotes the maximum precision. ...
"
Regards, Chuck
midrange.com
