Floating point comparison in Oracle

Question

I have a query like

SELECT
    some_field,
    SUM(float_field)
FROM
    some_table
GROUP BY
    some_field
HAVING
    SUM(float_field) <> 0

Is it safe to use such way of comparison in Oracle or it is better to use something like

ABS(SUM(float_field)) < PrecisionConstant

Just for case, float_field is not nullable field of FLOAT type

score 0 · Accepted Answer · answered Oct 27 '12 at 15:34

0

Well, the expression HAVING SUM(float_field) <> 0 will not restrict records where the SUM would be like 0.1 or -0.2.

Use the following expression if you want to achieve that:

HAVING TRUNC(SUM(float_field)) <> 0

answered Oct 27 '12 at 15:34

Mateus Schneiders

Thanks. Well, actual numbers that are stored in this column have no more than 3 decimal places but it is a kind of legacy table and I can't change the column type from FLOAT to NUMBER(X, 3). So I would like to know whether Oracle can give me the result which will have more then 3 decimal places like 0.000000001 so it will be treated as not equal to zero – Demarsch Oct 29 '12 at 07:32

1 Answers1