Re: NVL

"J Huntey Palmer" <jhp_at_dontspam.spam> a écrit dans le message de news: 125qsn7f41fk61a_at_news.supernews.com...
| WHat is the difference between sum(nvl(col1,0)) and nvl(sum(col,0)) and
| nvl(sum(nvl(col9,0)))?
|
| Which is the best to use to ensure that there are no snafus?
|
| Thanks

They give the same result but the last one with 2 nvl is useless.

SQL> select * from t;

       COL

1 row selected.

SQL> select sum(nvl(col,0)), nvl(sum(col),0), sum(col) from t; SUM(NVL(COL,0)) NVL(SUM(COL),0) SUM(COL) --------------- --------------- ----------

              0 0

1 row selected.

Moreover if there is only one non-null value then there is no difference with sum(col):

SQL> insert into t values (1);

1 row created.

SQL> select * from t;

       COL

         1

2 rows selected.

SQL> select sum(nvl(col,0)), nvl(sum(col),0), sum(col) from t; SUM(NVL(COL,0)) NVL(SUM(COL),0) SUM(COL) --------------- --------------- ----------

              1 1 1

1 row selected.

Regards
Michel Cadot Received on Sun May 07 2006 - 00:35:07 CDT