What happens if you test it in Firebird 2.1.

I believe that last week or two weeks ago something similar came up
(might have been in the dev-mailinglist).

The problem is that Firebird 1.5 (maybe 2.0 as well) treats the literals
different than quoted string literals of the same number, resulting in
truncation instead of rounding.

Mark

Jacek Borowski wrote:

> Hi,
>
> We have some problem with rounding numbers. Problem occures on Linux only. On Windows, results are OK. I've tested FB 1.5.3 - 1.5.5 on CentOs5.1 and 5.3.
>
> select cast(67.865 as numeric(10,2)) from rdb$database returns correct result: 67.87
> select cast(cast(67.865 as numeric(10,2)) as numeric(18,4)) from rdb$database - correct result: 67.87
>
> select * from zaok(67.865,2) - returns 67.86 - wrong result
>
> procedure zaok:
>
> create or alter procedure ZAOK (
> LICZBA double precision,
> ZAOKR integer)
> returns (
> KWOTA numeric(18,4))
> AS
> declare variable wyw varchar(100);
> declare variable i integer;
> declare variable c varchar(1);
> declare variable d integer;
> begin
> kwota=cast(liczba as numeric(18,4));
>
> if (zaokr>=0) then begin
> wyw='SELECT cast(cast('||:LICZBA||' as numeric(18,'||ZAOKR||')) as Numeric(18,4)) FROM rdb$database';
> execute statement wyw into :KWOTA;
> end else begin
> zaokr=-1*zaokr;
> zaokr=zaokr-1;
> wyw=cast(liczba as varchar(100));
> i=f_strpos('.',wyw);
> if (i>0) then wyw=substr254(wyw,1,i-1);
> i=strlen(wyw);
> if (i>zaokr+1) then begin
> if ( substr254(wyw,i-zaokr,i-zaokr) in ('0','1','2','3','4') ) then begin
> kwota=cast( substr254(wyw,1,i-(zaokr+1))||F_strrepeat('0',(zaokr+1)) as numeric(18,4) );
> end else begin
> c=substr254(wyw,i-(zaokr+1),i-(zaokr+1));
> d=cast(c as integer)+1;
> kwota=cast( substr254(wyw,1,i-(zaokr+2))||d||F_strrepeat('0',(zaokr+1)) as numeric(18,4) );
> end
> end else begin
> kwota=0.0;
> end
> end
> suspend;
> end
>
> I think, this statement should return the same result as the first two examples
> wyw='SELECT cast(cast('||:LICZBA||' as numeric(18,'||ZAOKR||')) as Numeric(18,4)) FROM rdb$database';
> execute statement wyw into :KWOTA;
>
> I have no idea whats wrong ?
>
> Regards,
> Jacek Borowski

--
Mark Rotteveel