Re: SQL precision problem

On Fri, Feb 12, 2016 at 1:08 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

The problem occurs because the calculation is performed under the cover as
float.
One rule when working with float is, if the (calculated) maximum length is
reached, decimal positions are cut at the end.

Is "float" IBM's terminology? I really wish they would use terminology
that is compatible with everyone else in the world. What you are
describing is nothing like what is commonly understood as float. The
whole point of floating point is that the total precision is fixed,
but the decimal point floats around freely so as to produce the most
graceful degradation possible during calculations. The *actual values*
are what matter in true floating point, not the declared size of the
variable. And OP's actual values are well within even 16-bit floating
point precision (which is hardly ever used anymore; nowadays the
smallest you usually see is 32-bit).

Indeed, if the calculation were performed using the floats that the
rest of the computing world uses, there would be much less confusion.

If you change the cast function to a precicion of 24 or , it calculates
correctly.
Select x.*,
(A - B) / B * 100.00 as RESULT
from (Values(cast(1.5267 as decimal(24, 4)), cast(1,6 as decimal(12, 4)))) x
(a, b);;

You're saying that if he had asked for LESS precision, then his result
would have been accurate!?

I have tried several times to understand IBM's precision rules for
intermediate calculations (in RPG, but it sounds like this SQL stuff
is the same or similar), with very little success. I have even
followed along with official documentation, and pencil and paper to
try out examples, and I still have not been able to really get it.

John Y.