At 05:38 PM 20/09/2007, you 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?

Because, under SQL rules, an integer/integer operation results in an
integer. In all such operations, any decimal parts in the result
thus disappear. So, for example, 1/3 = 0. If you want a more
accurate result you would supply decimal parts for one or both
operands, e.g. 1.000/3 = .333; 1.00/3.0 = .333, 1.00/3.0000 =
.333333, and so on.

>Must the table definition for qty
>and price be defined as DECIMAL as well?

For price, yes, of course, either DECIMAL(p,s) or NUMERIC(p,s). For
qty, only if you have to allow for parts of shares to be
purchased. Can a buyer purchase half a share? I dunno, but I think not.

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

With fixed decimal types, you define e.g. NUMERIC(18,4) which gives
you a maximum of 18 significant figures, of which the last 4 are
fractional. (4 places of decimal are a common requirement around
financial transactions). The 4 in this example will take up to four
places of decimal. If you multiply or divide two such numbers, your
result will have 8 places of decimal.

./heLen