
	> isnull() is on our TODO list.

	It should be called NVL() from "Null VaLue" like in Oracle and
Informix.
	isnull() would suggest a boolean return value to me.

	Andreas

Wed, 9 Dec 1998 11:29:32 +0100 
Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
AW: [HACKERS] isnull() or is it?t
Zeugswetter Andreas IZ5 wrote:
> 
>         > isnull() is on our TODO list.
> 
>         It should be called NVL() from "Null VaLue" like in Oracle and
> Informix.
>         isnull() would suggest a boolean return value to me.
> 


With SyBase, IsNull(X,Y) returns X if X is not null, and Y
if X is null.

Example:

SELECT reference, name, IsNull(price, 0.00)
FROM t_items


reference      name           price
------------ --------------- -------
W95          Windows95        190.00
WNT400       Windows NT 4.0   490.00
LX           Linux              0.00


rather than:

SELECT reference, name, price,
FROM t_items


reference      name           price
------------ --------------- -------
W95          Windows95        190.00
WNT400       Windows NT 4.0   490.00
LX           Linux             NULL


--
H.Lefebvre

Wed, 09 Dec 1998 14:56:12 +0000
"H.Lefebvre" <hlefebvre@easynet.fr>
Re: AW: [HACKERS] isnull() or is it?t
> 
> 	> isnull() is on our TODO list.
> 
> 	It should be called NVL() from "Null VaLue" like in Oracle and
> Informix.
> 	isnull() would suggest a boolean return value to me.
> 
> 	Andreas
> 
> 

NVL added to TODO.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 9 Dec 1998 10:07:23 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: AW: [HACKERS] isnull() or is it?t
> Zeugswetter Andreas IZ5 wrote:
> > 
> >         > isnull() is on our TODO list.
> > 
> >         It should be called NVL() from "Null VaLue" like in Oracle and
> > Informix.
> >         isnull() would suggest a boolean return value to me.
> > 
> 
> 
> With SyBase, IsNull(X,Y) returns X if X is not null, and Y
> if X is null.

We should support both isnull() and nvl().  On the TODO list now.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 9 Dec 1998 10:10:07 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: AW: [HACKERS] isnull() or is it?t
> With SyBase, IsNull(X,Y) returns X if X is not null, and Y
> if X is null.

Which is identical in behavior to the SQL92-defined function
COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
Postgres development tree, to be available in the next release. Should
we also have the less capable IsNull() available too? COALESCE() has the
nice feature that it takes an unlimited number of arguments, returning
the first non-null result.

                   - Tom

Wed, 09 Dec 1998 15:21:53 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: AW: [HACKERS] isnull() or is it?t
> > With SyBase, IsNull(X,Y) returns X if X is not null, and Y
> > if X is null.
> 
> Which is identical in behavior to the SQL92-defined function
> COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
> Postgres development tree, to be available in the next release. Should
> we also have the less capable IsNull() available too? COALESCE() has the
> nice feature that it takes an unlimited number of arguments, returning
> the first non-null result.

Oh, NVL and isnull are not standard?  Then let's just use coalesce.  I
will remove them from the TODO list.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 9 Dec 1998 10:47:08 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: AW: [HACKERS] isnull() or is it?t
On Wed, 9 Dec 1998, Bruce Momjian wrote:

> > > With SyBase, IsNull(X,Y) returns X if X is not null, and Y
> > > if X is null.
> > 
> > Which is identical in behavior to the SQL92-defined function
> > COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
> > Postgres development tree, to be available in the next release. Should
> > we also have the less capable IsNull() available too? COALESCE() has the
> > nice feature that it takes an unlimited number of arguments, returning
> > the first non-null result.
> 
> Oh, NVL and isnull are not standard?  Then let's just use coalesce.  I
> will remove them from the TODO list.

Surprises me too.  I was under the (incorrect) impression that isnull
was standard.  

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================




Wed, 9 Dec 1998 11:16:42 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: AW: [HACKERS] isnull() or is it?t
> Surprises me too.  I was under the (incorrect) impression that isnull
> was standard.

If it is, my books don't bother mentioning it. It would be pretty easy
to implement, if it *is* standard, since it is just a subset of
COALESCE() behavior.

But I'm a bit worried about the recent trend toward coding in
non-standard extensions as *alternatives* to standards-based behavior
just because a specific product has it. I know it's convenient for some,
but there are *lots* of products which have these kinds of things, each
in a different way, and at some point we would collapse under the weight
of trying to be all things at once.

Not naming any names, but *$ products seem to be the worst violators of
the simplest standards. One might ascribe this to simple incompetence,
except for their no-longer-in-doubt agenda of subverting standards in a
so-far-successful drive for market dominance. For example, Access
produces code of the form
  val = NULL
as an equivalent to the SQL92-standard
  val IS NULL

Where my Date book has a sentence:
"It is not possible to specify NULL explicitly as an operand of a
conditional expression - e.g., 'WHERE X = NULL' is illegal."

We now have a parser which violates that, for the sake of Access
compatibility. Of course, we do support another extension to SQL92 which
violates another rule in the same area:
"It is not possible to specify NULL explicitly as a select-item -- e.g.,
'SELECT NULL' is illegal."

Oh well. otoh, perhaps some of this would be legal for SQL3, which has a
somewhat more developed concept of NULL...

                - Tom

Wed, 09 Dec 1998 18:17:21 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: AW: [HACKERS] isnull() or is it?t
If it is interesting to someone, we can partially emulate COALESCE
right now as:

create function coalesce(integer) returns integer as
'declare
        nonullo alias for $1;
begin
     if nonullo then
        return nonullo;
     else
        return 0;
     end if;
 end;
' language 'plpgsql';
CREATE

select *,coalesce(comm) from emp where comm is null;

ename|empno|job       |  hiredate|sal      |comm|deptno|level| mgr|coalesce
-----+-----+----------+----------+---------+----+------+-----+----+--------
BLAKE| 7698|MANAGER   |1981-05-01|$2,850.00|    |    30|    3|7782|       0
JONES| 7900|CLERK     |1981-12-03|$950.00  |    |    30|    2|7782|       0
CLARK| 7844|SALESMAN  |1981-09-08|$1,500.00|    |    10|    2|7839|       0
(3 rows)

-Jose'-

>> > > With SyBase, IsNull(X,Y) returns X if X is not null, and Y
>> > > if X is null.
>> > 
>> > Which is identical in behavior to the SQL92-defined function
>> > COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
>> > Postgres development tree, to be available in the next release. Should
>> > we also have the less capable IsNull() available too? COALESCE() has the
>> > nice feature that it takes an unlimited number of arguments, returning
>> > the first non-null result.
>> 
>> Oh, NVL and isnull are not standard?  Then let's just use coalesce.  I
>> will remove them from the TODO list.

VV> Surprises me too.  I was under the (incorrect) impression that isnull
VV> was standard.  



Thu, 10 Dec 1998 14:41:17 +0100
Sferacarta Software <sferac@bo.nettuno.it>
Re[2]: AW: [HACKERS] isnull() or is it?t
On a somewhat related topic, the development parser also now supports
the SQL92-standard NULLIF(), which behaves in an opposite sense to the
common IsNull() under discussion. That is, it tests the first argument
against the second, and then returns NULL if they are equal. Weird.

                      - Tom

Thu, 10 Dec 1998 16:45:08 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: AW: [HACKERS] isnull() or is it?t
>
> If it is interesting to someone, we can partially emulate COALESCE
> right now as:
>
> create function coalesce(integer) returns integer as
> 'declare
>         nonullo alias for $1;
> begin
>      if nonullo then
>         return nonullo;
>      else
>         return 0;
>      end if;
>  end;
> ' language 'plpgsql';
> CREATE

    Pardon,  but  you  still  misuse the fact, that PL/pgSQL's IF
    expression is implicitly casted into a boolean.  That's  only
    possible for integer values.

    Please use

        IF nonullo ISNULL THEN
            RETURN 0;
        ELSE
            RETURN nonullo;
        END IF;

    instead,  because this would work for other types (like text,
    varchar etc.)  too.

    Since PL functions can be overloaded (like SQL functions), it
    would  be possible, but currently not that performant :-(, to
    create such a function for all types required.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Thu, 10 Dec 1998 21:26:41 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: Re[2]: AW: [HACKERS] isnull() or is it?t