Aggregates losing decimals

Classic Servoy
1

I have a simple aggregate that sums the total of the quantity (usually a quantity is an integer or a decimal like 0.5, 2.5 etc).
This aggregate is shown from the master form (“offers”, accessing the related aggregate present on lineitems).

The quantity column is set to number, but if I have 1 and 1.5, the aggregate shows me 4.

I didn’t notice before, but I’m not 100% sure that is related to my recent upgrade to 3.5rc2.

Anyway, my current configuration is:
Version 3.5 rc2-build 507
Java version 1.5.0_07-87 (Mac OS X)
Sybase 9.02

2

so the database column is an number (not integer) and does store decimals?
but 1.5 + 1 becomes 4? that seems very strange to me
or do you mean 1.5 + 2 becomes 4? that would also be strange because a if really integers are used then 1.5 + 2 = 3.5 should just become 3.

3

jcompagner:
so the database column is an number (not integer) and does store decimals?

Yes. The quantity column stores values like 0.5; 1, 1.5 etc.

The aggregate is a sum of this quantity.

The field is shown on the form via a relation that counts the quantity of the confirmed items only, so it’s something like
OrderID → OrderID
k → ordered (an integer set to 1 when the items is confirmed)
According to 3.5, the relation is set to INNER JOIN

At the moment, I get:
1.5 + 2 = 4
1.5 + 2 + 0.5 = 5

I tried removing every format option, but it only changes to 5.00 to 5

4

1.5 + 2 + 0.5 is 5? I think you mean something like 1.5 + 2 + 1?

do you have a test case for us that you can attach to a case?
what database are you using?

5

jcompagner:
1.5 + 2 + 0.5 is 5? I think you mean something like 1.5 + 2 + 1?

do you have a test case for us that you can attach to a case?
what database are you using?

Sybase. No, the values are what I wrote: 1.5, 2 and 0.5.

I also checked the logs: can this be related to this syntax?

00:00:045 10 00:00:004 Aggregate SQL select sum(cast(importo as numeric(19,0))) as totimpo, sum(cast(qt as numeric(19,0))) as totqt from rigprev_cli where prevcliid = ?
6

Riccardino:
I also checked the logs: can this be related to this syntax?

00:00:045 10 00:00:004 Aggregate SQL select sum(cast(importo as numeric(19,0))) as totimpo, sum(cast(qt as numeric(19,0))) as totqt from rigprev_cli where prevcliid = ?

Using this syntax (totally generated by Servoy) in AquaDataStudio also gives me the same error. Using sum(qt) gives 4, sum(cast(qt as numeric(19,0))) gives 5

7

when you cast them like this I can imagine that it makes sense…

Is it possible to cast outside the sum instead of inside?

8

we are busy with it. It only does this on specific databases because in the sybase (and ms sql server) the sum couldn’t return values bigger then the column type itself. So we first cast it then do the sum. But for sybase this cast is wrong.

9

We only add the cast for some databases.
We need it in sybase because when the sum of an int column does not fit in an int we had overflow exceptions.

For 3.5 rc4 we will change the cast type to be different for double columns and so preventing the rounding.

Rob

10

rgansevles:
For 3.5 rc4 we will change the cast type to be different for double columns and so preventing the rounding.

Excellent. Can’t wait to see RC4, then :)

11

Does this also happen in 3.1.6.???

12

HJK:
Does this also happen in 3.1.6.???

No, in 3.5 only.
Rob