Rounding variant

Radu Sky wrote:

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

Claudio Valderrama C. answers:

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