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)
),
/* 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
),
/* 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 ;
This mailing list archive is Copyright 1997-2026 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
