Numeric overflow occurred during computation?

Database
Enthusiast

Numeric overflow occurred during computation?

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

why???

thks!

12 REPLIES

Re: Numeric overflow occurred during computation?

Hi,

When I checked the same, am not getting numeric overflow error.
sel cast(400.000000000 * 100000000 as decimal(38,8));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

(400.000000000*100000000)
----------------------------------------
40000000000.00000000

Priyanka!!

Junior Contributor

Re: Numeric overflow occurred during computation?

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;

Dieter

Enthusiast

Re: Numeric overflow occurred during computation?

Dieter:

   decimal(38,8) is bigger than decimal(18,2),why it overflow?

Enthusiast

Re: Numeric overflow occurred during computation?

dieter:

when i excute sql as bellow:

sel type(400.000000000 * 100000000),

result is decimal(18,9).

thks

Junior Contributor

Re: Numeric overflow occurred during computation?

You multiply first, this exceeds the dec(18,9) and thus fails before the CAST.

So CAST one of the operands before the multiplication.

Dieter

Enthusiast

Re: Numeric overflow occurred during computation?

dieter:

When cast to deciaml(18,2),it's ok?

I'm so puzzle with this....

Junior Contributor

Re: Numeric overflow occurred during computation?

Of course it's ok, simply try it.

Base the definition on your actual datatypes/data/requirements.

Dieter

Enthusiast

Re: Numeric overflow occurred during computation?

Dieter:

        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?

Junior Contributor

Re: Numeric overflow occurred during computation?

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)

Dieter