rules for SQL division and rounding?

just want to divide qtyShip by number of months. And round the result to 2
decimal places.

but this SQL does not round. Returns whole numbers.
decimal( case when a.numMonthsActive = 0 then 0
else ROUND(a.sumQtyShip / a.numMonthsActive,2) end,
8,2) ShipQtyPerMonth

To get the rounded results, I had to cast the sumQtyShip value to
decimal(7,0):

this rounds correctly:
decimal( case when a.shipQtyPerMonth = 0 then 99
else round(decimal(a.onHandNetQty,7,0) / a.shipQtyPerMonth,0) end
,11,0) monthsSupply,

What would be the cause of the first example not rounding but the 2nd one
does?

It was even more confusing in that when I run a simple SQL statement,
select the two columns, divide one by the other and apply the round
function. The result rounds correctly. Only when I run the full statement,
with a bunch of With clauses, do I get the incorrect result. Kind of like
the optimizer is placing the qtyShip in an integer field, maybe.

Here is the complete SQL statement. Part of procedure that returns a
result set.

DECLARE C1 CURSOR FOR
with t1 as (
select a.cono, a.whid, a.itcl, a.itsc,
a.purch_design_color design_color,
sum(a.salesAmt) salesAmt, sum(a.costAmt) costAmt,
sum(a.salesQty) salesQty
from itmsav1 a
where a.cono = inCono and a.whid = inWhid
and a.monthdate between
date(inFromMonthDate) and date(inToMonthDate)
and bomco_purchItno_activeCode(a.itno) = 'ACTIVE'
and ( inItcl = ' ' or a.itcl = upper(inItcl) )
and ( inItsc = ' ' or a.itsc = upper(inItsc))
and ( indesign_color = ' '
or a.purch_design_color like '%' || trim(indesign_color) ||
'%' )
group by a.cono, a.whid, a.itcl, a.itsc, a.purch_design_color
)

select a.*,
( select b.imitd1 from ITMST b
where substr(b.imitno,1,4) || '-' || substr(b.imitno,5,4)
= a.design_color
fetch first row only ) design_color_desc,
( select c.desc from orctlv2 c
where a.itcl = c.itcl ) itcl_desc,
( select d.desc from orctlv1 d
where a.itcl = d.itcl and a.itsc = d.itsc ) itsc_desc,
decimal(dense_rank( ) over (
partition by a.itcl, a.itsc order by a.salesAmt DESC ),5,0)
as rank_amt
from t1 a
order by a.cono, a.whid, a.itcl, a.itsc,
decimal(dense_rank( ) over (
partition by a.itcl, a.itsc order by a.salesAmt DESC ),5,0) ;

DECLARE C2 CURSOR FOR
with t1 as (
select a.cono, a.whid, a.itcl, a.itsc,
a.purch_design_color design_color,
sum(a.salesAmt) salesAmt, sum(a.costAmt) costAmt,
sum(a.salesQty) salesQty
from itmsav1 a
where a.cono = inCono and a.whid = inWhid
and a.monthdate between
date(inFromMonthDate) and date(inToMonthDate)
and bomco_purchItno_activeCode(a.itno) = 'ACTIVE'
and ( inItcl = ' ' or a.itcl = upper(inItcl) )
and ( inItsc = ' ' or a.itsc = upper(inItsc))
and ( indesign_color = ' ' or
a.purch_design_color like '%' || trim(indesign_color) || '%' )
group by a.cono, a.whid, a.itcl, a.itsc, a.purch_design_color
),

t2 as (
select a.*,
( select b.imitd1 from ITMST b
where substr(b.imitno,1,4) || '-' || substr(b.imitno,5,4)
= a.design_color
fetch first row only ) design_color_desc,
( select c.desc from orctlv2 c
where a.itcl = c.itcl ) itcl_desc,
( select d.desc from orctlv1 d
where a.itcl = d.itcl and a.itsc = d.itsc ) itsc_desc,
decimal(dense_rank( ) over (
partition by a.itcl, a.itsc order by a.salesAmt DESC ),5,0)
as rank_amt
from t1 a ),

/* summarize item sales by itno within the date range. */
t3 as (
select a.purch_design_color design_color,
bomco_getPurchItno(a.itno) itno,
sum(a.salesAmt) salesAmt, sum(a.salesQty) salesQty
from itmsav1 a
where a.cono = inCono and a.whid = inWhid
and a.monthdate between
date(inFromMonthDate) and date(inToMonthDate)
and bomco_purchItno_activeCode(a.itno) = 'ACTIVE'
and a.purch_design_color in ( select b.design_color
from t2 b )
group by a.purch_design_color, bomco_getPurchItno(a.itno)
),

/* summary ITBAL, number months active, qty ship during date range. */
t3b as (
select bomco_getPurchItno(a.itno) itno, a.itcl, a.itsc,
max(b.ibvnno) vnno,
decimal(sum(b.ibohq1),8,0) onHandQty,
decimal(sum(b.ibaqt1),8,0) allocQty,
decimal(sum(b.ibpoq1),8,0) onPoQty,
decimal(sum(b.ibqsyd),8,0) ytdQty,
decimal(sum(b.ibisyd),8,0) ytdAmt,
decimal(sum(b.ibqsly),8,0) lastYrQty,
decimal(sum(b.ibisly),8,0) lastYrAmt,
min(hsdet_FirstInvDate(0,a.itno,NULL,NULL, inwhid))
firstSaleDate,
sum(hsdet_SumShipQty(0,a.itno,vFromDate,vToDate, inwhid))
sumQtyShip,
max(hsdet_NumMonthsActive(0,a.itno,vFromDate,vToDate, inwhid))
numMonthsActive
from itmstv1 a
join itbal b on a.itno = b.ibitno and b.ibwhid = '2'
where ( inItcl = ' ' or a.itcl = upper(inItcl))
and ( inItsc = ' ' or a.itsc = upper(inItsc))
and ( indesign_color = ' '
or a.purch_design_color like '%' || trim(indesign_color) ||
'%' )
group by a.itcl, a.itsc, bomco_getPurchItno(a.itno)
),

t4 as (
select a.cono, a.whid, a.itcl, a.itsc, a.design_color,
a.salesamt design_salesAmt, a.salesqty design_salesQty,
a.design_color_desc, a.itcl_desc, a.itsc_desc,
a.rank_amt design_amt_rank,
b.itno, b.salesAmt item_salesAmt, b.salesQty item_salesQty,
c.imus5a itemwid, c.imus5b itemht, c.immc01 shape,
c.IMITd1 ITD1, c.IMitd2 ITD2,
d.vnno vnno,
d.onHandQty, d.allocQty, d.onPoQty,
d.ytdQty, d.ytdAmt, d.lastYrQty, d.lastYrAmt,
d.firstSaleDate, d.sumQtyShip, d.numMonthsActive,

iahst_GetRecentRcvDate(inWhid,b.itno) recvDate,

/* open po qty. get from PODETv1. */
decimal( coalesce(( select sum(e.openQty)
from podetv1 e
where e.itno = b.itno ),0),8,0) openPoQty,

/* po qty that is on the water. get from
PODETv1. */
decimal( coalesce(( select sum(e.openQty)
from podetv1 e
where e.itno = b.itno and e.containerid <> ' ' ),0),
8,0) waterPoQty,

/* soonest PO due
date. */
( select f.rrdate from podetv1 f
where f.itno = b.itno
order by f.rrdate
fetch first row only ) duedate,

/* UOM of the item. */
itclsc_uom( a.itcl, a.itsc ) uom,

/* forecast qty. forecast notes. */
coalesce(( select c.baseqty
from wia700plan c
where c.itno = b.itno ),0) proposePoQty,
wia700for_foreNotes( b.itno ) foreNotes

from t2 A
join t3 b
on a.design_color = b.design_color
join itmst c on b.itno = c.imitno
join t3b d on b.itno = d.itno
),

/* calculate onHandNetQty. */
t5 as (
select a.*,
decimal(
a.onhandqty - a.allocQty + a.openPoQty,8,0) onHandNetQty,

/* po qty that is not on the water. */
a.openPoQty - a.waterPoQty remainPoQty,

/* calc qty shipped per month. */
decimal( case when a.numMonthsActive = 0 then 0
else ROUND(decimal(a.sumQtyShip,9,0) / a.numMonthsActive,2)
end,
8,2) ShipQtyPerMonth
from t4 a
),

/* calculate columns based on columns in the T5 table. */
t6 as (
select a.*,
decimal( case when a.shipQtyPerMonth = 0 then 99
else round(decimal(a.onHandNetQty,9,0) / a.shipQtyPerMonth,0) end
,11,0) monthsSupply,

/* on hand after proposed po qty. */
a.onhandNetQty + a.proposePoQty afterOnhand,

/* after purchase months supply. */
decimal( case when a.shipQtyPerMonth = 0 then 99
else round(decimal(a.onHandNetQty + a.proposePoQty,9,0)
/ a.shipQtyPerMonth,0) end
,11,0) afterMonthsSupply,

/* system calculated PO qty. Based on 4 months demand. */
wia700_calcPoQty( a.shipQtyPerMonth, a.onHandNetQty ) calcPoQty
from t5 a
)

select a.*
from t6 a
order by a.cono, a.whid, a.itcl, a.itsc,
a.DESIGN_amt_RANK, a.itno ;