Subject Re: [firebird-support] Re: Error in stored procedure. Why? (more) Helen Borrie 2004-07-09T00:07:59Z
At 10:41 PM 8/07/2004 +0000, you wrote:
>> > This line raises an error
>> >> DS = (1 - D1/100) * (1 - D2/100) * (1 - D3/100) * (1 - D4/100) > >
>> > This two lines works fine
>> > DS = (1 - D1/100) * (1 - D2/100) * (1 - D3/100) ;
>> > DS = DS * (1 - D4/100) ;
>>
>> The 'precision' adds up as you go along. Your Numerics (18,6) when
>> multiplied will become (18,12) and then (18,18) and then booom!
>> Rewrite the expression into smaller parts so the intermediate
>results can
>> be kept 'within reason'.
>>
>
>I have tried
> DS = (1 - D1/100) ; <-- 18,6 becomes 18,8
> DS = DS * (1 - D2/100) ; <-- 18,8 becomes 18,16
> DS = DS * (1 - D3/100) ; <-- BOOM!! ( result would be 18,24, an invalid number)
> DS = DS * (1 - D4/100) ;
>But but sometimes i get again the error.
>What can i do ?

Cast the results (and, if necessary, the operands) to keep all numbers within the required precision range.

For example:

DS = CAST((1 - D1/100) AS NUMERIC(18,6)) ;
DS = CAST((DS * (1 - D2/100)) AS NUMERIC(18,6)) ;
DS = CAST((DS * (1 - D3/100)) AS NUMERIC(18,6)) ;
DS = CAST((DS * (1 - D4/100)) AS NUMERIC(18,6)) ;

/heLen