I guess it is because of the precision.

63.34/100 =0.6334, but casted as decimal(6,2) it will become 0.63 (the first number used in the calculation will decide the precision).

0.63*(33-5) = 0.63*28 = 17.64

Kind regards
Poul

Fra: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] På vegne af si_carter_987654321
Sendt: 3. juni 2012 13:24
Til: firebird-support@yahoogroups.com
Emne: [firebird-support] Error in calculation within stored procedure



Hi,

The following code is normally within a stored procedure but I have converted it to execute block with the same results.

In this scenario each salon has a discount on products (33%), they can give their customers a discount (5%) which is taken from their overall discount (leaving 28% for salon), the discount is from the net ammount of the customer spend, in the case below Total - (vat + shipping) or (79.95 - (12.66 + 3.95)) = 63.34.

I then need to calculate how much discount the salon can have, in this case is should be 17.73.

the problem: when executing the code below it returns 17.64 instead of 17.73.

If anyone can have a look and let me know where I am going wrong it would be appreciated

SET TERM ^ ;

execute block
returns (
discount decimal(6, 2),
customer_discount decimal(6, 2),
customer_spend decimal(6, 2),
salon_discount decimal(6, 2)
)
AS
DECLARE VARIABLE vVAT DECIMAL(6, 2);
DECLARE VARIABLE vShipping DECIMAL(6, 2);
DECLARE VARIABLE vInvoiceTotal DECIMAL(6, 2);
DECLARE VARIABLE vSalonDiscount INTEGER;
DECLARE VARIABLE vCustomerDiscount INTEGER;
BEGIN
vVat = 12.66;
vShipping = 3.95;
vInvoicetotal = 79.95;
vSalonDiscount = 33;
vCustomerDiscount = 5;

customer_spend = vInvoiceTotal - (vVat + vShipping);
salon_discount = (customer_spend / 100) * (vSalonDiscount - vCustomerDiscount);
SUSPEND;
END^

SET TERM ; ^


My settings are:
Windows 7 Pro
Server: WI-V2.f.1.26351 Firebird 2.5
ODS Version: 11.2
Page size: 8192
Pages: 17418
Size on disk: 136.08MB
Page buffers: 2048
Read only: false
Dialect: 3
Default character set: ISO8859_1
Sweep interval: 20000
Forced writes: yes

kind regards

Si



[Non-text portions of this message have been removed]