Under the cover SQL knows only 2 numeric data types, Integer and Float.
If both numeric values are integer the result will be again an integer
value. If you divide 2 integers the result is again an integer, i.e. decimal
positions are truncated.
So you need to convert at least one of 2 digits (in either way the
denominator) into a decimal value.
Also a constant value without decimal positions is interpreted as Integer
For example:
2 / 3 will return 0 (because both are interpreted as integer)
2/Cast(3 as Dec(5, 0)) will return 0.6666666 (the denominator is converted
into a decimal)
2 / 3.0 will return 0.6666666 (the denominator is interpreted as decimal)
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of Steve
Richter
Sent: Dienstag, 23. Oktober 2018 21:05
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: 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 ;
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
