At 08:38 AM 19/12/2003 +0800, you wrote:

>Hi when I execute this sql query
>
>select (("TotalAmount" * (1-("Discount"*0.01)) * (1-("Discount2"*0.01))
>* (1-("Discount3"*0.01)) - "Less" - "Adjustment")) as "GrandTotal"
>from "invoice"
>
>Iam getting a Interger Overflow error. The result of an integer
>operation cause the most significant bit of the result to carry. But I
>don't have any integer field here ... All of them are Numerics(15,2)
>expcept for the discounts field which are numerics(4,2)
>
>
>
>Hint:
>1. I limit the number of records to be included with "where
>"TotalAmount" < 100000" and everything works well. I have a
>"TotalAmount" = 560000 as the biggest value.
>
>2. I have try this sql and it works on all records
>
>select "TotalAmount"* (1-("Discount"*0.01)) * (1-("Discount2"*0.01))
>from "invoice"
>
>or
>
>select "TotalAmount"* (1-("Discount2"*0.01)) * (1-("Discount3"*0.01))
>from "invoice"
>
>What is this error?

It isn't weird - it is expected behaviour. Numerics are just scaled
integers. Each multiplication or division increases the scale of the
result, until you overflow the precision (stored as precision 18 but
limited by your definition). For simple examples:

1.50 * 1.50 = 2.2500
(1.50 * 1.50) / (.01 * .01) = 22500.00000000
I suggest you use CAST(YourResult as numeric(15,2)) throughout, to keep the
scale correct; or use double precision numbers and build in your own
rounding algorithm; or convert some of your multiplier factors to integer
and then divide and recast the final result.

If you haven't done so far, read Geoff Worboys' paper at the IBO TechInfo
site on this topic.

I don't think use F_fixedpoint is a great solution. It returns a string,
which might or might not need recasting for further calculations,
anyway. Plus it means installing FreeUDFLib, which makes your database
non-portable.

Complex expressions need to be designed in terms of the accuracy of
results, not just in terms of whether they do or don't cause data type
exceptions in the engine. How you resolve this really gets down to running
lots of *good* typical testing data using various algorithms, and comparing
the results with calculator results, examining variances, etc., etc.

/heLen