Hi Antz

All you have to do is change one line in the Stored Procedure:
- ValueAmount = (in_qty * in_price) / 100.0;

The number 100 is an integer, so the result will be an integer.

The number 100.0 is a decimal, so the result will be a decimal.

Ove


--- In firebird-support@yahoogroups.com, "Anthonie"
<anthonie_botha@...> wrote:

>
> 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.
>
> Many thanks in advance,
> Antz
>