Hi, I have doubt about the sql statements below:
sel cast(400.000000000 * 100000000 as decimal(18,2)) ---OK
sel cast(400.000000000 * 100000000 as decimal(38,8)) ---error,overflow...
The default maximum number of decimal digits for an expression is based on dbscontrol General field 13: MaxDecimal
When you submit a
sel type(400.000000000 * 100000000)
you'll probably get dec(15,9) or dec(18,9).
The best solution would be changing MaxDecimal to 38, but this has do be done by your DBA after checking for possible side-effects for existing applications (and it requires a restart). On the other hand this is usually safe, when a query/application encountered this error it's already doing a workaround:
sel cast(400.000000000 as decimal(38,8)) * 100000000;
You multiply first, this exceeds the dec(18,9) and thus fails before the CAST.
So CAST one of the operands before the multiplication.
I do know this is ok and I have test it.But why?
When cast to decimal(38,8),you say cast should be excuted after the multiplication.
So, when cast to decimal(18,2), I think that multiplication should be excuted first.
Why it is ok?
cast(400.000000000 * 100000000 as decimal(38,8))
--> multiplication first, maximum number of digits for the result of the calculationbased on MAXDECIMAL: 18 (fails ), then CAST
cast(400.000000000 as decimal(38,8)) * 100000000
--> CAST first to 38 digits, then multiplication (success)
Same for any cast to a decimal with sufficient precision like decimal(18,2)