Arithmetic overflow occurred

[william-paul_philibert]

Bonjour,

   I am trying a simple math calcul that my tabletop cheap calculator can easily handle but Sybase just can't ....

declare @totalsize float

select @totalsize=(894720*4096/1048576)

Arithmetic overflow occurred ... Sybase errorcode 3606

No matter what type I use (numerci, bigint, int, nameit), nothing is working, my database is simple unable to handle this calcul.

Anyone has an idea what's going on!?

[former_member188958 · Accepted solution]

The problem is that you may be specifying the dataype of the result set, but you aren't specifying the datatype for the literal numbers.  ASE is defaulting these to INTs, and  (INT) (894720* (INT) 4096 overflows what an INT can hold before it would be converted to a float.

If you added a ".0" to each of these values, ASE would default them to a float or numeric datatype.

You could also use an explicit convert(float, ...) around each literal value.

It is never a bad idea to cast literal values to a specific datatype.

[william-paul_philibert]

I thank you guys, both of you, well explained and fix my issue using the convert(float) as I don't always know what the numbers will be.

This was, at the very least, real tricky, this is a lesson I won't forget.

Thanks again to you.

[Former Member]

Like *all* DBMS's, ASE attempts to translate literals into datatypes - e.g. you couldn't even begin the above in Java without specifying types.    ASE attempts based on the value - and for most numeric values without a decimal, it treats as an 'int' unless >2B.    So in your case you have:

int * int /int

....but the int*int overflows int.

Simple fix

select @totalsize=(894720.0*4096/1048576)

Now, because of the .0, ASE treats the first value as a float - and following standard ANSI rules float * int = float ... /int = float...