The round function seem have problem on this situation :
ROUND (5.625 TO 2) return 5.62. For me, the result must be 5.63
ROUND (5.6251 TO 2) return 5.63. This is correct.
I share some code with Oracle. With Oracle ROUND (5.625,2) return 5.63

Is a bug ?

Thanks

Eric,

No, I believe that what you are seeing is the effect of a representation error, that is normal for floating point values.

ROUND ( CAST(5.625 AS DECIMAL(19,3)) TO 2) gives 5,63
ROUND ( CAST(5.625 AS FLOAT) TO 2) gives 5,62

Without the CAST( AS DECIMAL) the constant 5.625 is parsed as a float and internally represented as a float before the rounding. What happens then is that the value being rounded is not exactly 5.625 but the floating point  representation of that value, with a tiny round-off error, that is normal for floating point values.

--
Fernando Dias
[Team Elevate]

Eric,

>The round function seem have problem on this situation :
>ROUND (5.625 TO 2) return 5.62. For me, the result must be 5.63
>ROUND (5.6251 TO 2) return 5.63. This is correct.
>I share some code with Oracle. With Oracle ROUND (5.625,2) return 5.63
>
>Is a bug ?

You also need to be aware of how Delphi does rounding. Delphi uses what
is known as Bankers Rounding which means round to the closest even
number. This is how Bankers Rounding works:

12.345 Rounds to 12.34
12.455 Rounds to 12.36
12.365 Rounds to 12.36

Here is a blog post I wrote after getting hammered on Stack Overflow
http://capecodgunny.blogspot.com/2012/06/rounding-currency-type-like-the
y-taught.html


--
Michael Riley
GySgt USMC Retired
http://capecodgunny.blogspot.com/

Michael,

EDB doesn't do Bankers Rounding:
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=ROUND

--
Fernando Dias
[Team Elevate]

I know the problem with FLOAT.
Thanks for yours answers.

I have translate my round function from Delphi to SQL.
It's not perfect but seem better with float.

What do you think ?

CREATE FUNCTION "OG_ARRONDI" (IN "VALEUR" FLOAT, IN "NB_DECIME" INTEGER)
RETURNS FLOAT
BEGIN
  DECLARE Temp FLOAT DEFAULT 0;
  DECLARE Temp10 FLOAT DEFAULT 0;
  DECLARE Decimale FLOAT DEFAULT 0;
  DECLARE Coef FLOAT DEFAULT 0;

  SET Coef = POWER(10 TO NB_DECIME + 1);
  SET Temp = Valeur * coef;
  SET Temp = TRUNCATE(Temp TO 0);
  
  SET Temp10 = Temp / 10;
  SET Temp10 = Temp10 - TRUNCATE(Temp10 TO 0);

  SET Decimale = Temp10 * 10;
  IF Decimale >= 5 THEN
    SET Decimale = 10 - Decimale;
    SET Temp = Temp + Decimale;
  ELSE
    SET Temp = Temp - decimale;
  END IF;

  SET Temp = Temp / coef;

  RETURN Temp;
END

Eric,

I didn't yet check your "OG_ARRONDI" rounding function - I will, and get back to you then.
Anyway, if what you need is to minimize round-off errors and you don't need more than 4 decimal places, I'd replace the FLOAT columns with DECIMAL columns where appropriate. That would definitely save you from having to worry about rounding errors.

--
Fernando Dias
[Team Elevate]

Eric,

I've just checked your rounding function, and there is a problem - it's not treating positive and negative values symmetrically. For example, it rounds 5.625 to 5.63 but -5.625 is being rounded to -5.62. For the rest it seems fine.
In any case, I've been looking more closely to the ROUND function in EDB and I do believe it can be improved... Let's wait and see what's Tim's opinion.

--
Fernando Dias
[Team Elevate]

Fernando

>I've just checked your rounding function, and there is a problem - it's not treating positive and negative values symmetrically. For example, it rounds 5.625 to 5.63 but -5.625 is being rounded to -5.62. For the rest it seems fine.

It depends on your definition of symmetric. In both cases its rounding up which seems symmetrical to me. If for negatives it produced -5.63 it would be rounding up for positives and down for negatives.

Roy

This is extact. I use my function only with positive number but it's easy to apply minor modification  for use with any number.
Now, i use ROUND DECIMAL for currency number (work very well) and my function for float positive number (work very well in this case).

I've tested on 300 000 rows calculation : none error


Thanks for all

Roy,

From Eric's posts I conclude that he does want to use the rounding method implemented by the ROUND function in EDB, at least he didn't say it isn't. His complaint was about round-off errors but not the rounding method in itself.
 
<< It depends on your definition of symmetric. >>

The rounding method implemented by ROUND in EDB is what is called "round half away from zero", that is considered a symmetric method in the sense that globally it's not biased as the bias introduced for positive numbers compensates the bias introduced for negative numbers. Thats what I meant for "symmetric" rounding - and it's not *my* definition.
Anyway, it seems it's not an issue for Eric as he just said he is using it only for positive numbers.
 
--
Fernando Dias
[Team Elevate]