--- In [email protected], <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.
