Subject Re: Count value on field
Author rogervellacott
A way I have got around this problem of nulls messing up calculations
is as follows:

1. define an SP

CREATE PROCEDURE ZEROIFNULL(N NUMERIC)
RETURNS
(R NUMERIC)
AS
BEGIN
IF (N IS NULL) THEN
R = 0;
ELSE
R = N;
SUSPEND;
END

2. Then use it in calculations (I have not done this in COMPUTED
fields, but I use it in SELECT SUM() quite often

eg - defining a computed field

MY_COMPUTED_FIELD COMPUTED BY ((SELECT R FROM ZEROIFNULL(MYFIELD1)) +
(SELECT R FROM ZEROIFNULL(MYFIELD2)) etc

and for queries

SELECT SUM((SELECT R FROM ZEROIFNULL(A.FIELD1))) FROM TABLEA A

(The apparently redundant brackets are necessary for some reason)

It's a bit wordy, but I prefer it to a UDF.