NVL

Returning data is the function of a SELECT statement, and it does it very well. But what do we do with that data where we don't know what it is - the dreaded null.

It is not valid to say that a null value returned from a select statement is incorrect. It is entirely possible that for the row of data returned that particular piece of data is unknown, or was unknown at data entry time.

We do however, have a problem when it comes to performing calculations with nulls. A null value in a column will not affect a sum or avg calculation though they treat it differently.

Running the following example:

create table t (x number);

insert into t values (1);
insert into t values (1);
insert into t values (1);
insert into t values (null);
insert into t values (1);

select sum(x) , avg(x) , count(x) from t;

gives.....

    SUM(X)     AVG(X)   COUNT(X)
---------- ---------- ----------
         4          1          4


1 row selected.

The average and count seem to be wrong. Actually - the way they are written they 'ignore' rows with null values. Sum on the other hand seems to assume that null means zero. Correct me if I am wrong.

We can affect the results by telling the SUM and AVG functions to 'fill-in' a value if there is a null by using NVL.

SELECT SUM (NVL (x, 0)), AVG (NVL (x, 0)), 
       COUNT (NVL (x, 0)), COUNT (*)
  FROM t;

SUM(NVL(X,0)) AVG(NVL(X,0)) COUNT(NVL(X,0))   COUNT(*)
------------- ------------- --------------- ----------
            4            .8               5          5


1 row selected.

And all because of a null..........