C:\Documents AND Settings\Nicolas>sqlplus gerardnico/Password
SQL*Plus: Release 10.2.0.4.0 - Production ON Wed Apr 8 15:41:24 2009
Copyright (c) 1982, 2007, Oracle. ALL Rights Reserved.
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND Real Application Testing options
gerardnico@orcl>create TABLE IHateNull (id number NOT NULL, val1 number NULL, val2 number NULL);
TABLE created.
gerardnico@orcl>insert INTO IHateNull VALUES (1,2,NULL);
1 row created.
gerardnico@orcl>insert INTO IHateNull VALUES (1,NULL,1);
1 row created.
gerardnico@orcl>insert INTO IHateNull VALUES (1,0,0);
1 row created.
gerardnico@orcl>insert INTO IHateNull VALUES (1,1,1);
1 row created.
gerardnico@orcl>select * FROM IHateNull WHERE ( val1 + val2 ) <> 0;
ID VAL1 VAL2
---------- ---------- ----------
1 1 1
gerardnico@orcl>select * FROM IHateNull WHERE ( nvl(val1,0) + nvl(val2,0) ) <> 0;
ID VAL1 VAL2
---------- ---------- ----------
1 2
1 1
1 1 1
