Subject | Re: Count value on field |
---|---|
Author | rogervellacott |
Post date | 2002-03-20T14:59:33Z |
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.
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.