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

On Sat, 13 Jun 1998 rmcm@compsoft.com.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'