Subject Re: [IBO] working with large numbers
Author Helen Borrie
At 06:48 PM 15/12/2006, you wrote:
>I prepared a query "select sum(amont*price) mtotal from orders " using ibsql.
>Actually the mtotal is no more than 99 999 999 999.999
>amount is defined as numeric(18,6)
>price is defined as numeric(18,6) on database table.
>The biggest number in amount is 156 000.56

= 156 000.560000

>The biggest number in price is 15 600.123

= 15 600.123000

Result: 2433627924.060000000000 (22 significant digits)

>When I run this query
>"ISC Error Code 335544779"
>"Integer overflow. The result of an integer operation caused the
>most significant bit of the result to carry"
>The error above occurs.
>How can I get rid of this ?

1) Don't define and store fixed decimals of unnecessarily large scale

2) Be aware that when you multiply or divide, the scale of the result
is the sum of the scales of the operands. Use CAST to keep results
within the precision range. You may need to use an intermediate cast
to double precision.

3) Read Geoff's paper about numeric types at the TechInfo page.