Re: rules for SQL division and rounding?

The problem is probably the CASE statement...

decimal(
case
when a.numMonthsActive = 0 then 0
else ROUND(a.sumQtyShip / a.numMonthsActive,2)
end,
8,2) ShipQtyPerMonth

specifically, the "then 0"

DB2 takes the data type from the first expression...

try "then 0.00"

tried the 0.00. Still same error.

decimal( case when a.numMonthsActive = 0 then 0.0
else a.sumQtyShip / a.numMonthsActive end,
8,2) ShipQtyPerMonth4,

but casting the a.sumQtyShip variable to decimal does work. Which is nuts
in that a.sumQtyShip is the SUM of the DECIMAL value returned by a function.

decimal( case when a.numMonthsActive = 0 then 0.0
else DECIMAL(a.sumQtyShip,9,0) / a.numMonthsActive end,
8,2) ShipQtyPerMonth4,

here is how sumQtyShip is calculated:
sum(hsdet_SumShipQty(0,a.itno,vFromDate,vToDate, inwhid))
sumQtyShip,

CREATE OR REPLACE FUNCTION hsdet_ShipQtyPerMonth( inCono
decimal(2,0),
inItno char(27), inFromDate date, inToDate date,
inWhid char(2))
RETURNS decimal(9,2)