Skip site navigation (1) Skip section navigation (2)

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

From: "Jose' Soares Da Silva" <sferac(at)bo(dot)nettuno(dot)it>
To: rmcm(at)compsoft(dot)com(dot)au
Cc: pgsql-general(at)PostgreSQL(dot)org, hackers postgres <pgsql-hackers(at)PostgreSQL(dot)org>
Subject: Re: [GENERAL] COALESCE() or NVL()
Date: 1998-06-17 12:03:43
Message-ID: Pine.LNX.3.96.980617114843.1509A-100000@proxy.bazzanese.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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'


In response to

pgsql-hackers by date

Next:From: Charles BennettDate: 1998-06-17 14:10:34
Subject: Re: [BUGS] Re: [HACKERS] pg_dump error
Previous:From: infotechsysDate: 1998-06-17 11:22:04
Subject: http://www.postgresql.org/devel-mlists.shtml

pgsql-general by date

Next:From: The Hermit HackerDate: 1998-06-17 17:21:14
Subject: User Gallery...
Previous:From: infotechsysDate: 1998-06-17 11:17:17
Subject: http://www.postgresql.org/supp-mlists.shtml

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group