Dear all,

Still related to the "money data type" thread recently, this morning i
found that in some cases, the following query would cause an integer
overflow error (Firebird 1.5.3, Win32, SuperServer):
...
select (qty * unit_price) total
from my_table;
...
both "qty" and "unit_price" is numeric(18,4).

From my testing, the 'limit' seems to be around 11-12 digits.
So :
case 1 : '123,456,789' * '100' is OK.
case 2 : '123,456,789' * '1000' will bomb.
case 3 : '123,456,789' * '10000' will bomb.

So i was wondering, is there a 'proper' way to write these types of
queries to get the 'maximum' range of numeric(18,4) types? (i.e. 14
digits in front of the decimal point).

"select cast( (qty * unit_price) as numeric(18,4))" doesn't work.

"select cast( (1e0 * qty * unit_price) as numeric(18,4))" doesn't work.

"select cast(qty as numeric(18,2)) * unit_price" works to a certain
extent (i.e. i can get case 2 to work, but not case 3 above).

Thank you very much in advance,
regards,
t.s.