At 05:53 PM 19/03/2004 +0700, you wrote:

>When I do this SELECT:
>
>SELECT sum( a.discount * b.price * b.quantity * ( 1 - b.discount2 ) * ( 1 -
>b.discount3 )) FROM a, b ......
>
>Discount, discount2, and discount3 - decimal(8,4)
>Price and Quantity - decimal(15,2)
>
>I found this error: "Integer overflow. The result of an integer operation
>caused the most significant bit of the result to carry."
>
>How to solve it?

With fixed precision numbers, each multiplication or division aggregates
the scale of both operands. Your calculation produces a result with a
scale of 16 - moving from left to right that's 4 + 2 + 2 + 4 + 4 - hence
the overflow.

Two possible solutions: use double precision numbers for the discount
factors and then cast your result; or reduce their scale to 0 (integer),
then round your result, then divide afterwards to move the scale back, and
cast the result.

/heLen