> Odd calculations in select statement
>
> Hello! I am new to this list, and I hope it's the right place for
> my question.
>
> My original intention was to round numeric values via SQL by
> multiplying them with 100, then casting the result to integer,
> and finally dividing the new integer by 100. Then I noticed this
> strange inconsistency. Here's my test scenario:
>
>
> create table testtable (testvalue numeric(15, 2));
> commit work;
> insert into testtable (testvalue) values (52.5);
>
>
> Running this query against any FB 1.5/2.0 engine...
>
>
> select
> testvalue * 1.19,
> cast(testvalue * 100 * 1.19 as integer),
> cast(testvalue * 1.19 * 100 as integer),
> cast(1.19 * 100 * testvalue as integer),
> cast(1.19 * testvalue * 100 as integer),
> cast(62.475 * 100 as integer)
> from testtable
>
> ...results in this:
>
> F_1 CAST CAST1 CAST2 CAST3 CAST4
> 62,475 6248 6247 6248 6247 6248
>
>
> I wonder whether this a known bug/issue? Any comments appreciated!
>
>
> Greetings
> Thomas

it's not an inconsistency - it's exactly as per the SQL standard
F_1 is correct
CAST is correct (rounded to integer)
CAST1 is correct since the first calc 52.5*1.19 returns numeric 15,2 (2
decimal places) *100 it's already lost precision
CAST2 is correct, precision of calc is always within 15,2 but integer cast
rounds to 8
CAST3 is correct, first calc gets truncated to numeric 15,2 (loses
preicision) then *100 and truncates to 7.
CAST4 is correct, using the numeric 5,3 * 100 return numeric 5.1, cast as
integer gets rounded.

Use Double Precision for your starting point if you want precision.
Alan