Re: SQL Challenge (treat NULL as 0)

<Scott.Allred@nsc.sprint.com> wrote: > I need to be able to perform an aggregate function on two columns where the > value of one column may be NULL. I am aware that Informix's definition of an > aggregate that includes a NULL is NULL, but does anyone know of a way to > represent NULL as ZERO so that the aggregate function will work. I also have database columns that are legitiately NULL (and used as such in some queries), but need to be treated as 0 in some queries. I've defined the following procedures (in dbaccess): CREATE PROCEDURE nonullint(start INTEGER) RETURNING INTEGER; IF start IS NULL THEN RETURN 0; ELSE RETURN(start); END IF; END PROCEDURE DOCUMENT "RETURNS 0 FOR NULL OR PASSED VALUE OTHERWISE"; CREATE PROCEDURE nonulldec(start DECIMAL(12,3)) RETURNING DECIMAL(12,3); IF start IS NULL THEN RETURN 0; ELSE RETURN(start); END IF; END PROCEDURE DOCUMENT "RETURNS 0 FOR NULL OR PASSED VALUE OTHERWISE"; I can now do SELECT (nonullint(xyz.someint) - nonullint(xyz.otherint)) .... and be guaranteed that the expression will not be NULL. Hope this helps. -- Mark Rafn dagon@halcyon.com http://www.halcyon.com/dagon/ Decadence is its own reward.