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

Classic Servoy
1

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?

[attachment=0]Schermafbeelding 2011-12-19 om 23.11.18.png[/attachment]

Schermafbeelding 2011-12-19 om 23.11.18.png
Schermafbeelding 2011-12-19 om 23.11.18.png781×608 20.4 KB

2

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:

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

Or if that errors on nulls use

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

or try

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.

3

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

4

Harjo:
…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.

5

:-) 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! :lol:

6

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