Subject Re: [firebird-support] rounding variant Claudio Valderrama C. 2006-05-13T09:37:31Z
"Radu Sky" <skysword76@...> wrote in message
news:<446085AC.7030502@...>...
> Hello,
>
> Could somebody please explain why these results? I tried googling and
> searching in NG but I couldn't find a logical explanation
>
> 12.34/27=0.45703703703703703703703703703704
>
> ISQL results:
>
> SELECT 12.34/27 from rdb\$database => 0.45
> SELECT CAST(12.34/27 AS double precision) from rdb\$database =>
> 0.4500000000000000
>
> SELECT CAST(12.34/27 AS DECIMAL(18,2)) from rdb\$database =>0.45
>
> Now(note the 27.0):
>
> SELECT 12.34/27.0 from rdb\$database => 0.457
> SELECT CAST(12.34/27.0 AS double precision) from rdb\$database =>
> 0.4570000000000000
>
> SELECT CAST(12.34/27.0 AS DECIMAL(18,2)) from rdb\$database =>0.46

This is normal in dialect 3 -> exact numerics. Integer/integer gives
integer, thus any fractional part is lost. This is described in the IB6 docs
available at ibphoenix site. If you want the old behavior, then use double
precision values or cast one of the operands:
select cast(121.2 as double precision) / 22.3 from rdb\$database;

If you just want more (limited) precision, use more decimal places.
select 121.000 / 22.3 from rdb\$database;
should produce four decimal places. Same for multiplication.

You can play with cast to round.

Result with five places:

SQL> select 121.0000 / 22.3 from rdb\$database;

=====================
5.42600

Result with six places:

SQL> select 121.00000 / 22.3 from rdb\$database;

=====================
5.426008

Result with six places, rounded to five places:

SQL> select cast(121.00000 / 22.3 as numeric(10, 5)) from rdb\$database;

CAST
=====================
5.42601

C.