Re: [GENERAL] COALESCE() or NVL()

On Sat, 13 Jun 1998 rmcm(at)compsoft(dot)com(dot)au wrote:

> I got sum(money attribute) to return 0.00 instead of NULL when there
> are zero tuples in class, by redefining the sum() aggregate to set
> initcond1 to 0.00. Perhaps you do something similar with your AVL().
>
> -- Replace existing sum(money) to return $0.00
> -- for zero instances
>
> drop aggregate sum money;
> create aggregate sum (sfunc1 = cash_pl, -- sum
> basetype = money,
> stype1 = money,
> initcond1 = '0.00');
>
What I need is a scalar function that, unfortunatelly hasn't an initcond1.
I don't know how to make a select like:

SELECT COALESCE(field) FROM table;
or
SELECT CASE
WHEN field IS NOT NULL THEN field
ELSE 0
END CASE
FROM table;

> Jose' Soares Da Silva writes:
> > Hi all,
> >
> > I'm looking for a function like COALESCE() or the Oracle NVL(),
> > to returns a ZERO value instead of a NULL value.
> > To have the result: NULL+1 = 1 instead of NULL+1 = NULL
> > Have PostgreSQL something like this ?
> > I tried to write it on C but I can't realize the beavior of NULLs,
> > I can't get that my program returns a zero instead of a null.
> > I'm not a C programmer, could somebody help me ?
> >
> > SELECT * FROM emp;
> > name |salary|age|dept
> > -----------+------+---+-----
> > Sam | 1200| 16|toy
> > Claire | 5000| 32|shoe
> > Bill | 4200| 36|shoe
> > Ginger | 4800| 30|candy
> > NULL VALUES| | |
> > (5 rows)
> >
> > SELECT name,NVL(salary)+100 AS dream FROM emp;
> > name |dream
> > -----------+-----
> > Sam | 1300
> > Claire | 5100
> > Bill | 4300
> > Ginger | 4900
> > NULL VALUES| <--- I expected 100 here.
> > (5 rows)
> > Thanks, Jose'