Thanks Helen and Dimitry. This definitely is useful to know. I thought that it would work like programming languages where, for example, combining an integer with a floating point would result into a floating point number.
Thanks again On Fri, Jan 26, 2018 at 8:52 PM Helen Borrie [email protected] [firebird-support] <[email protected]> wrote: > Salim Naufal wrote: > > > I have noticed the following inconsistencies in Firebird 3.02: > > [..] > > > > > I then tried: > > SELECT CAST(10000 AS NUMERIC(18,2)) * (1.00 - (1.00/1.11)) FROM > > RDB$DATABASE and got a much better result of 991.00000 > > > Finally, the correct result is retrieved using: > > > SELECT CAST(10000 AS NUMERIC(18,2)) * (CAST(1 AS DOUBLE PRECISION) > > - (CAST(1 AS DOUBLE PRECISION)/1.11)) FROM RDB$DATABASE > > > 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 > > - 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 > > - 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. > > Helen > > > -- Salim Naufal
