Re: SUM in Informix 7.20 vs. Informix 6.0

Kai Shen wrote: > > Dear all, > > we experienced some calculation problems with Informix 7.20 4GL on SCO > Unix 5.02. E.G. converting a cost value from Austrian Schilling to US > Dollar requires a multiplication with 12.11, say 1000.00*12.11. This > gave a result of 8900, whereas calculating 1000.00*12.10 resulted in > 12100 and 1000.00*12.12 = 12120. This strange behaviour only occurs with > i4GL and under Informix 7.20, not with iSQL or Informix 6.0. > > A similar problem was caused by the SUM within i4GL, when using SELECT > firstvalue, SUM(anyvalue) FROM anytable GROUP BY 1. > Sometimes (not always) this SUM would be wrong. > > Is it only us to have this problem? Yes, we are running Intel Pentium > Pro 200, but an error of 30% is a little bit too high, right? R7.xx made a change in the internal handling of aggregates. Most are now handled as DECIMAL and return DECIMAL. Isql and dbaccess have no trouble since they convert all numerics into DECIMAL anyway. If your 4GL code is retrieving these values into a float it looks engine's like the conversion routines are buggy on your platform. Try making your host variable a DECIMAL and convert to float with the library routines. At least you can verify that the calculation is correct by printing the DECIMAL host var. BTW: 7.xx now also returns a DECIMAL for count(*) since a fragmented table can contain 2^31 rows.