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


Fri, 12 Jun 1998 10:53:12 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
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

Sat, 13 Jun 1998 13:37:03 +1000 (AEST)
rmcm@compsoft.com.au
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'


Wed, 17 Jun 1998 12:03:43 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [GENERAL] COALESCE() or NVL()
At 12:03 PM 6/17/98 +0000, Jose' Soares Da Silva wrote:
>> Jose' Soares Da Silva writes:
>>  > 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)

SELECT name, NVL(salary, 0) + 100 AS dream FROM emp;

NVL() takes two values: the column/variable, and the value to use if NULL.

--
Robin Thomas
rthomas@azstarnet.com

Thu, 18 Jun 1998 13:44:48 -0700
Robin Thomas <rthomas@azstarnet.com>
Re: [GENERAL] COALESCE() or NVL()
Hello Robin,

giovedï¿½, 18 giugno 98, you wrote:

RT> At 12:03 PM 6/17/98 +0000, Jose' Soares Da Silva wrote:
>>> Jose' Soares Da Silva writes:
>>>  > 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)

RT> SELECT name, NVL(salary, 0) + 100 AS dream FROM emp;

RT> NVL() takes two values: the column/variable, and the value to use if NULL.

RT> --
RT> Robin Thomas
RT> rthomas@azstarnet.com
I don't think this work Robin, because there isn't such function on
PostgreSQL.
the only thing that I have is:

     function nvl(int4, int4) does not exist

Do you know how to implement it on PostgreSQL ?

Best regards,
 Jose'                            mailto:sferac@bo.nettuno.it



Wed, 8 Jul 1998 15:35:06 +0200
Sferacarta Software <sferac@bo.nettuno.it>
Re[2]: [GENERAL] COALESCE() or NVL()

I am trying to use the nvl function with no success
when I say:
 select bar, NVL(foo, 0) from nulltest;
I get the error:
ERROR:  function nvl(int4, int4) does not exist

Does anyone have any suggestions?
Summer


On Thu, 18 Jun 1998, Robin Thomas wrote:

> At 12:03 PM 6/17/98 +0000, Jose' Soares Da Silva wrote:
> >> Jose' Soares Da Silva writes:
> >>  > 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)
> 
> SELECT name, NVL(salary, 0) + 100 AS dream FROM emp;
> 
> NVL() takes two values: the column/variable, and the value to use if NULL.
> 
> --
> Robin Thomas
> rthomas@azstarnet.com
> 
> 


Thu, 16 Jul 1998 15:15:21 -0600 (MDT)
Summer  <summerd@cs.unm.edu>
Re: [GENERAL] COALESCE() or NVL()