# Re: SQL Division Conundrum

 ```   ``` Subject: Re: SQL Division Conundrum From: CRPence Date: Sun, 17 Mar 2013 20:55:42 -0700 List-archive: List-help: List-id: Midrange Systems Technical Discussion List-post: List-subscribe: , List-unsubscribe: ,

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.

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.