On 2018-01-26 19:51, Helen Borrie helebor@...
[firebird-support] wrote:

>> I am not familiar with the details of the SQL standard, is this the
>> proper was to round and calculate numeric values in SQL? 
>
> You really do need to familiarise yourself with the way SQL handles
> numbers, especially division. In short, with implicit casting:
>
> - the result of division of an integer by a number of any type is
> always integer, with banker's rounding

A division of an integer by any number is not always integer: if divided
by a numeric with scale, the result is a numeric with scale, if by a
floating point, then the result is a floating point.

And there is no bankers rounding ('half even' rounding) involved with
exact numeric division, it uses rounding by truncation; it stops when
the target scale is reached, and performs no rounding. Compare 1 / 8.00
(= 0.12) and 3 / 8.00 (= 0.37), if bankers rounding would be used, the
second result would have been 0.38.

> - the result of fixed numeric/fixed numeric gives FN where the scale
> of the decimal part is the sum of the scales of the operands

Note that the same rules apply for integers (where integers simply have
scale 0).

> - the result of FN/floating point or FN/FP gives FP
>
> Hence the reason why these last two examples got closer to the result
> you wanted while performing that integer division messed it up. I
> suggest you play around with your formula in isql until you get the
> precision and scale that you desire in your result. Don't try to use
> a GUI tool for such testing as it will probably "tidy up" the output
> for you and confuse the outcome.
>
> Note, if you match the scale of your operands throughout, you might
> get away with not having to cast anything. Implicit casting casts
> non-integer numbers as double precision. Any operands that are
> database columns are not cast implicitly - they use the type defined
> for them.

I'm not sure what you mean with implicit casting here (unless you talk
about division in dialect 1), but the results of calculations are the
same whether you use columns of a given type, or literals of the same
type. The only influence you can apply is using explicit casts, or
changing the precision or type of a literal.

The literals in Firebird are as defined in the SQL standard, so for
exact numerics:

<exact numeric literal> ::=
<unsigned integer> [ <period> [ <unsigned integer> ] ]
| <period> <unsigned integer>

So, no digits after the decimal period (or no decimal period) is an
exact numeric with scale 0 (an 'integer', or better a decimal(18,0)), n
digits after the decimal period means scale n (a decimal(18,n).

and for floating point:

<approximate numeric literal> ::=
<mantissa> E <exponent>

<mantissa> ::=
<exact numeric literal>

Mark