NULL to Zero

[John Q]

Is there any function that would change a NULL value to zero? I am writing a
view that need to calculate the net inventory by subtracting the export qty
from import qty. However, in case of no export, 1-Null=Null. Thanks.

[Erland Sommarskog]

[posted and mailed, please reply in news]

John Q (jo***@hkayp.org) writes:

Is there any function that would change a NULL value to zero? I am
writing a view that need to calculate the net inventory by subtracting
the export qty from import qty. However, in case of no export,
1-Null=Null. Thanks.

coalesce(val1, val2, ...., valn)

returns the first non-NULL value in the list.

(There is also a function isnull() which has a clearer name, but is
restricted to two parameters only. coalesce() is from the ANSI standard.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Q]

Erland,

Thank you very much. This function is very useful to me.

Best Regards,
Frederick

"Erland Sommarskog" <so****@algonet.se> ¦b¶l¥ó
news:Xn*********************@127.0.0.1 ¤¤¼¶¼g...

[posted and mailed, please reply in news]

John Q (jo***@hkayp.org) writes:

Is there any function that would change a NULL value to zero? I am
writing a view that need to calculate the net inventory by subtracting
the export qty from import qty. However, in case of no export,
1-Null=Null. Thanks.

coalesce(val1, val2, ...., valn)

returns the first non-NULL value in the list.

(There is also a function isnull() which has a clearer name, but is
restricted to two parameters only. coalesce() is from the ANSI standard.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp