Re: null value in queries to default in zero

On Tue, 2002-11-12 at 07:49, Dorward Villaruz wrote:
> select to_char(avg(f1),'FM999999.99') from test where f2 > 7 will yield
> to_char
> --------
>
> (1 row)
>
> is their a way to make the value zero if the return is null?

Sure. The easiest way is:

select to_char(coalesce(avg(f1), 0), 'FM999999.99')
from test
where f2 > 7

You can also do:

select to_char(case when avg(f1) is not null then avg(f1)
else 0
end, 'FM999999.99')
from test
where f2 > 7

coalesce returns the first argument to it that is not null, or null if
they all are. It's actually implemented using the case one, which is
more verbose but more flexible.

Scott