Re: SQL7919 - Data Conversion required on Fetch...

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