Re: Nulls in Informix
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>
}