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

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');



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'
 >                                |  |
 > ~~~~~~~~~~~~~~~~~~~~~~~~       |  |        ~~~~~~~~~~~~~~~~~~~~~~~~
 >           Progetto HYGEA   ----    ----    www.sferacarta.com
 >     Sfera Carta Software   ----    ----    sferac@bo.nettuno.it
 >        Via Bazzanese, 69       |  |        Fax. ++39 51 6131537
 > Casalecchio R.(BO) Italy       |  |        Tel. ++39 51  591054
 >

--
------------------------------------------------------------
Rex McMaster                            rmcm@compsoft.com.au
                                   rex@mcmaster.wattle.id.au
     PGP Public key: http://www.compsoft.com.au/~rmcm/pgp-pk