Sybase 11 error: SUM() out of range for destination

[Harjo]

Hi there,

some customer reported an error in our solution: SUM() out of range for destination,

So I looked into the log files, and there I see a simple query just failing:

SELECT SUM(filesize) FROM documents WHERE companyid = ? parameters: [1 ,type: java.lang.Integer]

So I started Sybase Central on the same server, and typed the same query:

SELECT SUM(filesize) FROM documents WHERE companyid = 1, and there I got the same the error.
the column filesize is just a simple: Integer....

I googled around, but can someone, help me out here?

Schermafbeelding 2011-12-19 om 23.11.18.png (35.03 KiB) Viewed 4584 times

[ROCLASI]

Hi Harjo,

I guess whats happening here is that the resulting value is larger than an integer can hold. So its 'out of range' of this data type. Integers are 4 Bytes so perhaps you should use a Bigint or a Double which are 8 Bytes.
I suggest you cast it to a double in your query like so:

Code: Select all

SELECT SUM(CAST(filesize as double)) FROM documents WHERE companyid = ?

Or if that errors on nulls use

Code: Select all

SELECT SUM(CAST(COALESCE(filesize,0) as double)) FROM documents WHERE companyid = ?

or try

Code: Select all

SELECT CAST(SUM(filesize) as double) FROM documents WHERE companyid = ?

Just try these and see which one is more efficient (or even valid since I don't have an ASA install handy to test this out).

Hope this helps.

[Harjo]

oke, thank you very much Robert, both first two, work great.
This was indeed a custom query, but if I had created this SUM inside Servoy, than Servoy itself had also used this CAST??

[ROCLASI]

...if I had created this SUM inside Servoy, than Servoy itself had also used this CAST??

I doubt Servoy does that for you. It's too specific. In that case you probably had to alter the column to change the datatype from integer to double.
But perhaps Rob Gansevles can answer this one better.

[Harjo]

I could not resist, so I created the same SUM in Servoy, and indeed, Servoy does the cast to double, automaticly!!
another reason, to leave the query over to Servoy!

[ROCLASI]

Interesting! I guess they do that by default then.
In any case good to know!