Setting Null values to Zero

[C. Lo]

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

[Mark C. Stock]

"C. Lo" <st********@hotmail.com> wrote in message
news:71**************************@posting.google.c om...
| 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

[Noshad Ahmed]

try using NVL function...hence replace all null values with zeros and
perform a sort.
"C. Lo" <st********@hotmail.com> wrote in message
news:71**************************@posting.google.c om...

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