Subject Calculations in firebird Anthonie 2007-09-20T07:38:33Z
This might seem as a silly question for the experts but for a novice
a tough one. Below the following table definition as well as the
part of the stored procedure for adding a record to the table. The
application is written to manage share portfolios.

CREATE TABLE purchase(
pur_id INTEGER NOT NULL,
pur_ticker_symbol CHAR(3) NOT NULL,
pur_date DATE NOT NULL,
pur_qty INTEGER NOT NULL,
pur_price INTEGER NOT NULL,
pur_share_value DECIMAL(7,2) NOT NULL,
pur_broker DECIMAL(4,2) NOT NULL,
pur_strate DECIMAL(3,2) NOT NULL,
pur_vat DECIMAL(4,2) NOT NULL,
pur_ust DECIMAL(3,2) NOT NULL,
pur_levy DECIMAL(3,2) NOT NULL,
pur_total DECIMAL(7,2) NOT NULL,
pur_certificate VARCHAR(15) ,
PRIMARY KEY(pur_id))

CREATE PROCEDURE proc_Purchase(
proc_action CHAR(1),
in_ticker CHAR(3),
in_date DATE,
in_qty INTEGER,
in_price INTEGER,
in_broker DECIMAL(4,2),
in_strate DECIMAL(3,2),
in_ust DECIMAL(3,2),
in_levy DECIMAL(3,2),
in_certificate VARCHAR(15))
AS
DECLARE VARIABLE ValueAmount DECIMAL(7,2);
DECLARE VARIABLE VatAmount DECIMAL(4,2);
DECLARE VARIABLE TotalAmount DECIMAL(7,2);

BEGIN
ValueAmount = (in_qty * in_price) / 100;
VatAmount = (in_broker + in_strate) * 0.14;
TotalAmount = ValueAmount + in_broker + in_strate +
VatAmount + in_ust + in_levy;

(and the rest of the procedure...)
END

The procedure works fine and no errors but when calculating the
ValueAmount, given the following two values as quantity and price,
730 and 652, the result of ValueAmount should be 4759.60 but for
some reason this result is rounded to 4759.00 (the 60c is
discarded). Why does this happen? Must the table definition for qty
and price be defined as DECIMAL as well?

The VatAmount and TotalAmount are calculated correctly (in a way,
using the incorrect ValueAmount) but has their decimals in place.