Thread: COALESCE() or NVL()
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
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
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'
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
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
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 > >
Where did you find an NVL function? I cannot find any sign of it in my 6.3.2. Cheers and thanks, Stephen. ======================================================================== Stephen Davies Consulting scldad@sdc.com.au Adelaide, South Australia. Voice: 61-8-82728863 Computing & Network solutions. Fax: 61-8-82741015