Re: Nulls in Informix
On Thu, 4 Dec 1997 dinyar@rocketmail.com wrote:
> There is a isnull function in sybase which replaces a NULL output to
> whatever we like -
> eg. select isnull(name,'no name')
> from tab_name
> would result in
> name
> ---------------------
> george
> sally
> no name
> panna
> dinyar
> no name
> (6 rows affected)
>
> Similarly there is a function called nvl in Oracle.
>
> Is there a similar function in Informix?
As standard, in the available versions, no.
NVL is being added to 7.30 of OnLine and should presumably make it into the
9.20 version (whatever that ends up being called). I'm not sure about the
9.13 version, but I think it is not going to be in there.
However, it is not the end of the world; it is not very hard (but not very
convenient either) to handle this with a stored procedure. Depending on
what you need in the way of output, you can adapt the following to suit
your purposes:
-- @(#)$Id: nvl.spl,v 1.1 1996/08/26 18:33:11 johnl Exp $
--
-- nvl_integer: return v1 if it is not null else return v2
CREATE PROCEDURE nvl_integer(v1 INTEGER, v2 INTEGER DEFAULT 0)
RETURNING INTEGER;
DEFINE rv INTEGER;
IF v1 IS NOT NULL THEN
LET rv = v1;
ELSE
LET rv = v2;
END IF
RETURN rv;
END PROCEDURE;
One way of improving things is to make a procedure which accepts VARCHARs
and returns VARCHARs:
CREATE PROCEDURE nvl(v1 VARCHAR(255), v2 VARCHAR(255))
RETURNING VARCHAR(255); DEFINE rv VARCHAR(255);
IF v1 IS NOT NULL THEN
LET rv = v1;
ELSE
LET rv = v2;
END IF
RETURN rv;
END PROCEDURE;
One minor disadvantage is that it is not possible to give a meaningful
default for all types, unlike the integer version.
Yours,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>