Hi Helen

Helen Borrie wrote:

>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
>
>

If that is the case why is it I always get a result of maximum of 3
decimal palces only

>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.
>
>

I have also try to use cast but still error.

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

I haven't read this topic ... Ins't this about IBO? I will read to get
some more idea.

>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
>
>

Thanks Helen :-)

James

>
>To unsubscribe from this group, send an email to:
>firebird-support-unsubscribe@yahoogroups.com
>
>
>
>Yahoo! Groups Links
>
>To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
>To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>Your use of Yahoo! Groups is subject to:
> http://docs.yahoo.com/info/terms/
>
>
>
>
>
>