It should be:

CAST(BRUTTO_AR*(:BEF_OSSZEG/:FOSSZEG) AS NUMERIC(18,2))
=========
¦¦ ISMAEL ¦¦
=========


----- Mensaje original -----
De: Svein Erling Tysvær
Para: firebird-support@yahoogroups.com
Enviado: miércoles, 11 de febrero de 2009 05:19
Asunto: RE: [firebird-support] Precision question


I don't think you can expect a division to return an exact value. Suppose I borrowed 1 euro from Martijn at the last Firebird Conference and should pay it back in three equal installments. 1.00/3 rounds to 33 eurocent, so Martijn would only get back 99 eurocents. If I did (3*1.00)/3 I would get 1.00, if I did 3*(1.00/3) I would get 0.99 (and if I did 3*(1.00/3.00) I think I would get 0.9999). I don't quite understand your problem (whether the result is too accurate or inaccurate), but maybe

CAST(BRUTTO_AR*cast(:BEF_OSSZEG/:FOSSZEG AS NUMERIC(18,2)) AS NUMERIC(18,2))

can be what you're looking for?
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of rapdirector
Sent: 10. februar 2009 22:50
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Precision question

Hi Friends!

In my previous post I had the following problem:

"I have a problem that I cannot solve. I have a procedure that takes
two input parameters, both represent amount of money according to one
order.
One of them represents the full amount, the other represents the
currently paid amount of money. I have to create invoice from the
orderitems based on the two input parameters. The method would be the
following: I create a quotient from the input parameters and each
item's gross value will be multiplied with this quotient. In this
case, summing the amount of multiplied values would be equal to the
currently paid amount. But this is not true. Here is the procedure.
Sorry for the hungarian naming, but the method would be seen from this
code snippet. How can I achieve, that summing the BRUTTO_ERTEK_ARANYOS
field would be equal to the input parameter BEF_OSSZEG?

The full amount of money is the TELJES_OSSZEG, the currently paid is
BEF_OSSZEG."

I solved the problem by dividing directly with the input parameters.
It works perfectly in 99 cases out of 100. In the rest of cases, the
summed result of divided values differs from the original value by
0.01. Can I achieve more precision? What datatype should I CAST to?
Here is the actual procedure. The same rules are valid as in the
previous post.

SUM(BRUTTO_ERTEK_ARANYOS) should be equal to BEF_OSSZEG input parameter.

CREATE PROCEDURE SZOLG_FOR_SZAMLA(
FID INTEGER,
BEF_OSSZEG NUMERIC(18,2))
RETURNS (
FSZ_ID INTEGER,
FOGLALAS_ID INTEGER,
SZOLG_ID INTEGER,
NEV VARCHAR(50),
VTSZ VARCHAR(20),
ARANY NUMERIC(18,2),
BRUTTO_AR NUMERIC(18,2),
NETTO_AR_ARANYOS NUMERIC(18,2),
BRUTTO_AR_ARANYOS NUMERIC(18,2),
MENNYISEG NUMERIC(10,2),
SZAZALEK NUMERIC(10,2),
BRUTTO_ERTEK NUMERIC(18,2),
NETTO_ERTEK_ARANYOS NUMERIC(18,2),
BRUTTO_ERTEK_ARANYOS NUMERIC(18,2),
ME CHAR(10),
PARAM_ID INTEGER,
SZOBA_ID INTEGER,
SZAMLAZVA NUMERIC(18,2),
SORSZAM SMALLINT,
MEGJEGYZES BLOB sub_type 1 segment size 80)
AS
DECLARE VARIABLE FOSSZEG NUMERIC(18,2);
BEGIN
SELECT BRUTTO_OSSZEG FROM FOGLALAS WHERE FOGLALAS_ID=:FID INTO :FOSSZEG;

FOR SELECT
FSZ_ID,
FOGLALAS_ID,
SZOLG_ID,
NEV,
VTSZ,
BRUTTO_AR,
0,
CAST(BRUTTO_AR*:BEF_OSSZEG/:FOSSZEG AS NUMERIC(18,2)),
MENNYISEG,
SZAZALEK,
ERTEK,
0,
CAST(ERTEK*:BEF_OSSZEG/:FOSSZEG AS NUMERIC(18,2)),
ME,
PARAM_ID,
SZOBA_ID,
SZAMLAZVA,
SORSZAM,
MEGJEGYZES
FROM FOGLALAS_SZOLG WHERE FOGLALAS_ID=:FID INTO
:FSZ_ID,
:FOGLALAS_ID,
:SZOLG_ID,
:NEV,
:VTSZ,
:BRUTTO_AR,
:NETTO_AR_ARANYOS,
:BRUTTO_AR_ARANYOS,
:MENNYISEG,
:SZAZALEK,
:BRUTTO_ERTEK,
:NETTO_ERTEK_ARANYOS,
:BRUTTO_ERTEK_ARANYOS,
:ME,
:PARAM_ID,
:SZOBA_ID,
:SZAMLAZVA,
:SORSZAM,
:MEGJEGYZES DO
BEGIN
NETTO_AR_ARANYOS=CAST(BRUTTO_AR_ARANYOS/1.20 AS NUMERIC(18,2));
NETTO_ERTEK_ARANYOS=CAST(BRUTTO_ERTEK_ARANYOS/1.20 AS NUMERIC(18,2));
SUSPEND;
END
END

TIA:Alex :-)




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