Re: Nulls in Informix

Yes, but you would have the significant problem that you would have to analyze every insert into the table and leave out any column that had a null value in your application. If it is included in the insert you get an error, not the default value. I think Date, Darwen and others are of the opinion that simpler solutions for this problem should be implemented in the engine. jayallen@auragen.com (Jay Allen) wrote: :You could also re-design the database so that it uses default :values instead of nulls: : :create table tabname :( : name char(100) default 'no name' NOT NULL; : ... :); : :or: : :CREATE TABLE tabname :( : name char(100) default '' NOT NULL; : ... :); : :Not always an option with legacy systems, but always a good design principle :given the theoretical and practical problems with 3VL (see writings :of Date, Darwen and others on this topic). : :-J- : :} :} 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> :} : Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org