--- In firebird-support@yahoogroups.com, <sherlyn_20@...> wrote:

>
> Hi. I have some users which prefer decimal to be larger size, and some prefer decimal scale larger. However firebird only support 18 size for the whole decimal. But when some data contain larger number or longer precision by perform arithmetic calculation in SQL it will prompt arithmetic overflow error. Although in certain case we can casting the decimal to be certain size and scale to solve the problem. But the problem might occurred again from time to time. For example, i have a sql as the following:
>
>
> SELECT CAST((DocAmt * CurrencyRate) / SQty AS DECIMAL(18, 6)) FROM SL_IV
>
>
> The fields column structure as below:
> DocAmt = Decimal(16, 2)
> CurrencyRate = Decimal(18, 8)
> SQty = Decimal(18, 4)
>
>
> When i run the SQL it will prompt error "Arithmetic overflow or division by zero has occurred.arithmetic exception, numeric overflow, or string truncation.
> numeric value is out of range."
>
>
> You may replay the errror by running the SQL below:
>
>
> SELECT CAST((46841080.00 * 1.00000000) / 400.0000 AS DECIMAL(18, 6))
> FROM rdb$database
>
> Hope you can provide solution for this problem. Appreciate with Thanks!
>

You can try:
SELECT CAST((cast(DocAmt as double precision) * CurrencyRate) / SQty AS DECIMAL(18, 6)) FROM SL_IV

Good luck & regards
Zhou X.B.