On 17 Mar 2013 17:24, Sam_L wrote:
Boss was trying to sum two decimal fields and get the percentage
of the sums. It looked easy, but the percentage always came out
as zero.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzdecarithmetic.htm
_i Decimal arithmetic in SQL i_
"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. The value of mp is 63 if:
* either w or y is greater than 31, or
* a value of 63 was explicitly specified for the maximum precision.
Otherwise, the value of mp is 31.
The symbol ms denotes the maximum scale. The default value of ms is 31.
ms can be explicitly set to any number from 0 to the maximum precision.
≥The symbol mds denotes the minimum divide scale. The default value of
mds is 0, where 0 indicates that no minimum scale is specified. mds can
be explicitly set to any number from 1 to the maximum scale.≤
The maximum precision, maximum scale, and minimum divide scale can be
explicitly specified on the DECRESULT parameter of the CRTSQLxxx
command, RUNSQLSTM command, or SET OPTION statement. They can also be
specified in ODBC data sources, JDBC properties, OLE DB properties, .NET
properties.
_i Addition and subtraction i_
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).
_i Multiplication i_
The precision of the result of multiplication is min (mp,p+p') and the
scale is min(ms,s+s').
_i Division i_
The precision of the result of division is (p-s+s') + max(mds, min(ms,
mp - (p-s+s') ) ). The scale is max(mds, min(ms, mp - (p-s+s') ) ). The
scale must not be negative."
I did some experimentation and here's a simulation that anyone can
run:
select
sum(CDTDUE) CDTDUE
, sum(BALDUE) BALDUE
, (sum(CDTDUE) / sum(BALDUE)) *100 pct
from qiws/QCUSTCDT
The DDL for the TABLE referenced, for the two columns referenced:
CDTDUE NUMERIC(6, 2)
BALDUE NUMERIC(6, 2)
The first two SUM <ed: aggregate functions> clearly show that the
fields are numeric with 2 decimal places.
Yes, but with precision 31; well, most likely, per the default rules.
For example, review the report from the modified request which gives
the length [precision] of the result for the evaluation of each expression:
select
length(digits( sum(cdtdue) ))
, length(digits( sum(baldue) ))
, length(digits( ( sum(cdtdue) / sum(baldue) ) * 100 ))
from qiws/qcustcdt
; -- report follows:
LENGTH LENGTH LENGTH
31 31 31
******** End of data ********
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcolsum.htm
_i SUM i_
"The SUM function returns the sum of a set of numbers.
.-ALL------.
>>-SUM--(--+----------+--numeric-expression--)-----------------><
'-DISTINCT-'
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:
* DECFLOAT(34) if the argument values are DECFLOAT(16).
* 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 about the values of p, s, and mp, see
_i Decimal arithmetic in SQL i_
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzdecarithmetic.htm
..."
The PCT field is zero, because it apparently is resulting as an
integer.
The effect is due to the loss of scale due to the attempt to maintain
precision, according to the rules for the SUM aggregates and the
division operator used in the expression.
sum(CDTDUE) -> sum(decimal(6, 2)) -> decimal(mp, 2) -> decimal(31, 2)
sum(BALDUE) -> sum(decimal(6, 2)) -> decimal(mp, 2) -> decimal(31, 2)
( sum(CDTDUE) / sum(BALDUE) )
-> ( decimal(31, 2) / decimal(31, 2) )
-> expression: ( decimal(p, s) / decimal(p', s') )
-> result: decimal( (p-s+s') + max(mds, min(ms, mp - (p-s+s') ) )
, max(mds, min(ms, mp - (p-s+s') ) )
-> decimal( 31 + max( 0, min( 31, 31 - (31-2+2) ) )
, max( 0, min( 31, 31 - (31-2+2) ) )
-> decimal( 31, 0 ) *oops, loss of scale*
( decimal( 31, 0 ) * integer )
-> ( decimal( 31, 0 ) * decimal( 11, 0 ) )
-> expression: ( decimal(p, s) * decimal(p', s') )
-> result: decimal( min (mp,p+p') , min(ms,s+s') )
-> decimal( min( 31, 31 + 11 ) , min( 31, 0 + 0 ) )
-> decimal( 31, 0 )
If I force the first sum in the division to be decimal, like this:
select
sum(CDTDUE) CDTDUE
, sum(BALDUE) BALDUE
, (decimal(sum(CDTDUE), 11, 2) / sum(BALDUE)) *100 pct
from qiws/QCUSTCDT
With that modification to the expression:
sum(BALDUE) -> sum(decimal(6, 2)) -> decimal(mp, 2) -> decimal(31, 2)
( decimal(11, 2) / sum(BALDUE) )
-> ( decimal(11, 2) / decimal(31, 2) )
-> expression: ( decimal(p, s) / decimal(p', s') )
-> result: decimal( (p-s+s') + max(mds, min(ms, mp - (p-s+s') ) )
, max(mds, min(ms, mp - (p-s+s') ) )
-> decimal( 11 + max( 0, min( 31, 31 - (11-2+2) ) )
, max( 0, min( 31, 31 - (11-2+2) ) )
-> decimal( 31, 20 ) *scale is way more than required*
( decimal( 31, 20 ) * integer )
-> ( decimal( 31, 20 ) * decimal( 11, 0 ) )
-> expression: ( decimal(p, s) * decimal(p', s') )
-> result: decimal( min (mp,p+p') , min(ms,s+s') )
-> decimal( min( 31, 31 + 11 ) , min( 31, 20 + 0 ) )
-> decimal( 31, 20 )
then pct comes out to 2.29787594861576292000
Scale is 20, as determined above should be the case.
If I instead force the second sum in the division to decimal, like
this:
select
sum(CDTDUE) CDTDUE
, sum(BALDUE) BALDUE
, (sum(CDTDUE) / decimal(sum(BALDUE),11,2) ) *100 pct
from qiws/QCUSTCDT
With this modification, the same issue as the original expression:
sum(CDTDUE) -> sum(decimal(6, 2)) -> decimal(mp, 2) -> decimal(31, 2)
( sum(CDTDUE) / decimal(11, 2) )
-> ( decimal(31, 2) / decimal(11, 2) )
-> expression: ( decimal(p, s) / decimal(p', s') )
-> result: decimal( (p-s+s') + max(mds, min(ms, mp - (p-s+s') ) )
, max(mds, min(ms, mp - (p-s+s') ) )
-> decimal( 31 + max( 0, min( 31, 31 - (31-2+2) ) )
, max( 0, min( 31, 31 - (31-2+2) ) )
-> decimal( 31, 0 ) *oops, loss of scale; same story as before*
( decimal( 31, 0 ) * integer ) *no recovery of scale with multiply*
then pct comes out as zero, again apparently an integer.
Again, the loss of scale according to the default rules.
So, apparently we are missing something in the SQL rules of
arithmetic.
Does anyone know what?
More aggressive casting on both sides of the division operator will
tend to help obtain better results; as seen having done so with the
dividend, but not only with the divisor. But even without that casting,
the DECRESULT option should assist; i.e. do not use the default of
DECRESULT=(31, 31, 0). Most notably, do not allow the /mds/ remain zero.
On V7R1, cume from around October. But I see the same results on
V5R3.
I was unable to get the DECRESULT to have any effect on v5r3 within
RExX. I rarely do anything other than cast all my intermediate results
to desired values, so I have rarely had a need to use the DecResult
specification, and so I am unsure if the effect is available properly
with embedded at that release... but I do expect the effect to be
available in supported releases.
midrange.com
