« Are you really 18 years old today?
Concours SQL »

take care of minus !

Imagine this script (10gR2) :

set echo on
select  BINARY_DOUBLE_INFINITY -
BINARY_DOUBLE_INFINITY from DUAL;

Run it and you would will get an expected result!

SQL> select BINARY_DOUBLE_INFINITY -
> BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY
----------------------
                   Inf

The issue in sqlplus is that - at the end of line means “query continues next line”. The correct answer of Inf-Inf is Nan. 

SQL> select BINARY_DOUBLE_INFINITY
-BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY
---------------------------------------------
                                          Nan

This entry was posted on Monday, March 3rd, 2008 at 17:23 and is filed under oracle, sql, sqlplus. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

2 Responses to “take care of minus !”

  1. Nicolas Says:
    March 5th, 2008 at 12:13

    Thanks to recall us that point, but the problem is not limited with BINARY_DOUBLE_INFINITY usage :
    SQL> select 1 -
    > 1 from dual;
    select 1 1 from dual
    *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected
    …where 0 would be expected.

    All the best !

  2. Laurent Schneider Says:
    March 5th, 2008 at 13:53

    ok course, you could also have
    SELECT SAL-
    COMM FROM EMP;

    :)

Leave a Reply

Use <code> and </code> to post code