Re: SQL precision problem

I don't believe "float" is the right term.

As it implies the use of floating point types which are not involved during
fixed decimal arithmetic.

The rules for SQL are a little bit different than the ones for RPG. In
particular,
"The symbol mp denotes the maximum precision. The value of mp is 63 if:
- either p or p' is greater than 31, or
- a value of 63 was explicitly specified for the maximum precision.
Otherwise, the value of mp is 31."

With the original statement, you're only dealing with a maximum precision
of 31. If you change the precision of A to 32, the system will then use 63
digits for maximum precision, giving you the answers you expect.

with Values as (
select cast(1.5267 as decimal(32,4)) as A, cast(1.6 as
decimal(12,4)) as B
from SYSIBM.SYSDUMMY1
)
select Values.*, (A - B) / B * 100.00 as RESULT
from Values

Results (some spaces left out):
A B C
1.5267 1.6000 4.5812500000000000000000000000000-


Note that precision is the total number of digits, both left and right of
the decimal point. Scale is the number of digits to the right of the
decimal. Both SQL and RPG will by default drop decimal digits in order to
provide more digits to the left.
(p,s)
(A-B) --> (27,4)
(A-B) / B --> (31,0)

This also gives the results you'd expect.

with Values as (
select cast(1.5267 as decimal(31,4)) as A, cast(1.6 as
decimal(12,4)) as B
from SYSIBM.SYSDUMMY1
)
select Values.*
, cast((A - B) as dec(5,4)) / B * 100.00
as RESULT
from Values

The take away is be careful dealing with small values with large precision.


Charles

On Fri, Feb 12, 2016 at 2:43 AM, John Yeung <gallium.arsenide@xxxxxxxxx>
wrote:

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.
--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.