Re: Setting Null values to Zero

"C. Lo" <starshipdb_at_hotmail.com> wrote in message news:713a6263.0403061026.3dfed2bb_at_posting.google.com...
| Hi
|
| I have a situtation where a query returns a table in which some of the
| values are null. When I sort the results, the null values are treated
| as greater than the other numerical results. Is it possible to set the
| database treat the null values as zero?
|
| Thanks

no, and you wouldn't want a database-wide (or even a session-wide) setting, since NULL is a much different value from 0

what you need to use though is either the NULLS FIRST keywords on the order by (NULLS LAST is the default), or, if you really want the nulls converted to 0's in the results, use the nvl() function

SQL> select ename, sal, comm
  2 from emp
  3 order by comm nulls first, sal;
...

ENAME SAL COMM
---------- ---------- ----------

SMITH              -1
Adams            1100
MILLER           1300
CLARK            2450
JONES            2975
FORD             3000
SCOTT            4000
KING             5000
TURNER           1500          0
JAMES             950         22
ALLEN              16        300
WARD             1250        500
MARTIN           1250       1400

SQL> select ename, sal, nvl(comm,0) as commission   2 from emp
  3 order by nvl(comm,0), sal;
...

ENAME SAL COMMISSION
---------- ---------- ----------

SMITH              -1          0
Adams            1100          0
MILLER           1300          0
TURNER           1500          0
CLARK            2450          0
JONES            2975          0
FORD             3000          0
SCOTT            4000          0
KING             5000          0
JAMES             950         22
ALLEN              16        300
WARD             1250        500
MARTIN           1250       1400

;-{ mcs Received on Sat Mar 06 2004 - 20:40:44 CET