Subject | Re: [firebird-support] Re: Error in stored procedure. Why? (more) |
---|---|

Author | Helen Borrie |

Post date | 2004-07-09T00:07:59Z |

At 10:41 PM 8/07/2004 +0000, you wrote:

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

>> > This line raises an errorCast the results (and, if necessary, the operands) to keep all numbers within the required precision range.

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

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