NULL to Zero

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.[posted and mailed, please reply in news]

John Q (johnq@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

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

Best Regards,
Frederick

"Erland Sommarskog" <sommar@.algonet.se> bl
news:Xns93EC5BCD76E8Yazorman@.127.0.0.1 g...
> [posted and mailed, please reply in news]
> John Q (johnq@.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, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp