> No one? My (probably unjustified) fear is that since the variables are
> stored in a four byte primitive instead of a packed-decimal like format,
> I may randomly run in to this.
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
> Sent: Tuesday, March 06, 2012 12:08 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Precision in stored procedures
>
> If a stored procedure has three variables
>
> DECLARE VARIABLE A NUMERIC(18,3);
> DECLARE VARIABLE B NUMERIC(18,3);
> DECLARE VARIABLE C DOUBLE PRECISION;
>
> And a function is evaluated with these values
>
> A = B / C;
> A = 7.3 / 0.839080459770115
>
> Then the value stored in A will be 8.7 not 8.70000000000119 (a firebird
> stored procedure debugger) or 8.6999999999999994041095890410959 (windows
> 7 calculator), correct?

All used data types above map internally to a 64-bit data type, so 8
bytes. If an operation involves both a fixed numeric operand and a
floating point operand, the result is a DOUBLE PRECISION, which is
again, 8 bytes. The result is internally casted to the datatype of A, a
NUMERIC (18, 3), which also involves rounding.

A few examples in a dialect 3 database with isql (the pure truth):

1) Via EXECUTE BLOCK (PSQL)

SQL> set term !! ;
SQL> execute block returns (a numeric (18, 3))
CON> as
CON> declare b numeric(18, 3);
CON> declare c double precision;
CON> begin
CON> b = 7.3;
CON> c = 0.839080459770115;
CON> a = b / c;
CON> suspend;
CON> end!!

A
=====================
8.700

SQL> set term ; !!


2) A single SELECT statement

SQL> select cast(cast(7.3 as numeric(18, 3)) / cast(0.839080459770115 as
double precision) as numeric(18,3 )) from rdb$database;

CAST
=====================
8.700


3) The result of 2) inserted into a table field of type NUMERIC (18, 3)


SQL> select * from t5;

A
=====================
8.700


Does this help?



--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/