values(CURRENT DECFLOAT ROUNDING MODE);
set current decfloat rounding mode round_half_even;
with t1 (sw1,total,amount) as
(
values ( 0,cast(134572755.52 as decimal(18,2)),
cast(2199070.74 as decimal(17,2)) )
)
select
coalesce(round((cast(789450 as decimal(17,2)) *
amount
/ NULLIF(total,0)) * 100.00
/ NULLIF(130050515.34,0),2),0) as r1
from t1;
with t1 (sw1,total,amount) as
(
values ( 0,cast(134572755.52 as decimal(18,2)),
cast(2199070.74 as decimal(17,2)) )
)
select
coalesce(round((cast(789450 as decimal(17,5)) *
amount
/ NULLIF(total,0)) * 100.00
/ NULLIF(130050515.34,0),2),0) as r2
from t1;



Results in:

R1
---------------------------------
0.00

1 record(s) selected.



R2
---------------------------------
0.01000

1 record(s) selected.

The final result depends from the precision of a element of the
computation that in bot cases has the same significant value.

Is there not a wrong optimization shortcut in the computation made by
sql/db2?

Bernard (Dhooghe)

DB2 uses casting based on every part of the expression.

http://publib.boulder.ibm.com/infoce...Fr0000736.html

Can be solved by decfloat casting so that the computation is done in
decfloat.

Bernard (Dhooghe)