Select where number equals Infinity

Question

In SQL, how to select the rows of a table where a column (datatype : number) equals Infinity on Oracle 10g ?

select * from MYTABLE where MYCOLUMN = Infinity;

Answer 1

From Laurent Schneider:

select * from MYTABLE where MYCOLUMN = binary_double_infinity;

Or with an implicit cast, just:

select * from MYTABLE where cast(MYCOLUMN as binary_double) = binary_double_infinity;

Or using the is infinite floating point condition:

select * from MYTABLE where cast(MYCOLUMN as binary_double) is infinite;

I would attach an SQL Fiddle, but as Laurent noted, "expect a lot of bugs with your oracle clients"; this works in SQL Developer, but SQL Fiddle gets a numeric overflow.

Answer 2

Let's see first how to get Infinity :

SQL> SELECT 1/0F COL FROM DUAL
  2  /

       COL
----------
       Inf

Now, let's look at the comparison :

SQL> WITH DATA AS(
  2  SELECT 1/0F COL FROM DUAL)
  3  SELECT * FROM data WHERE col = binary_double_infinity
  4  /

       COL
----------
       Inf

Update : Thanks to Alex, the is infinite clause is also an option.

I am on 12.1.0.1.

The same query with is infinite clause :

SQL> WITH DATA AS(
  2  SELECT 1/0F COL FROM DUAL)
  3  SELECT * FROM data WHERE col is infinite
  4  /

       COL
----------
       Inf