Re: SQL Division Conundrum

[Sam_L <lennon_s_j@xxxxxxxxxxx>]

It might.  But we're not seeing why the result of dividing two numbers 
that contain decimal places results in (apparently) an integer.

Sam


On 3/17/2013 8:32 PM, Booth Martin wrote:
> Would it help to multiply one of the fields by 100 before the division
> instead of multiplying after the division?
>
> On 3/17/2013 7:24 PM, 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.
> >
> > 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 first two sum statements clearly show that the fields are numeric
> > with 2 decimal places.
> >
> > The PCT field is zero, because it apparently is resulting as an integer.
> >
> > 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
> >
> > then pct comes out to 2.29787594861576292000.
> >
> > 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
> >
> > then pct comes out as zero, again apparently an integer.
> >
> > So, apparently we are missing something in the SQL rules of arithmetic.
> >    Does anyone know what?
> >
> > Thanks,  Sam
> >
> > On V7R1, cume from around October.  But I see the same results on V5R3.