Hi,

At February 23, 2006, 6:42 PM, Luis Madaleno wrote:

> But it also fails using a CAST:

> new.fld3 = CAST((new.fld1 * new.fld2) AS NUMERIC(18,6));

As Ivan already said, the intermediate result of new.fld1 * new.fld2
will still be NUMERIC(18,12), before the CAST. And, the behavior is
according to the SQL Standard, so it can't be considered a bug. What
can be asked in the feature requests is to support 128 bit values,
because currently NUMERIC is only up to a 64 bit value(just verify
in the feature request tracker to see if it isn't already listed).

> What is the best field definition for currency values, with a precision
> of at least 6 digits?

One way would be to create a UDF to make those calculations. The
other would be to cast your values as DOUBLE PRECISION values before
the multiplication, but you might loose some accuracy in the decimals,
since it's an approximate value and not a precise value. So, if you
would be looking for precise calculations, you'll have to create a UDF
or try to find one that will work for you.

--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Consultants Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)