Choose where the number is Infinity

Question

In SQL, how to select table rows where the column (datatype: number ) is equal to 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, simply:

 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 a SQL Fiddle, but, as Laurent noted, "expect a lot of errors with your oracle clients"; this works in SQL Developer, but SQL Fiddle gets a numerical overflow.

Answer 2

First look at how to get Infinity :

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

Now 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 

Refresh . Thanks to Alex, the suggestion is infinite also an option.

I am on 12.1.0.1 .

The same query with the sentence is infinite :

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