How do I avoid NULL sums? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I avoid NULL sums?

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-08-05 : 12:18:09

As part of a much larger query, I am attempting to add the value of two columns of type money. Depending on the rest of the query, the values in either column can be NULL, or a single column can be NULL, or both columns can contain money values.

What I am trying to avoid is a result of NULL when either TSNI or TI has values, but when I run the query on actual data I get results similar to below:

TSNI Value	TI Value	Result
----------	--------	------
5.00		10.00		15.00
NULL		10.00		NULL
5.00		NULL		NULL

Would anyone have any ideas to point me in the right direction?

CASE subTSNI.TotalShippedNotInvoiced
	WHEN NULL THEN
		(CASE subTI.TotalInvoiced
			WHEN NULL THEN 0
			ELSE (SELECT subTI.TotalInvoiced)
		END)
	ELSE
		(CASE subTI.TotalInvoiced
			WHEN NULL THEN (SELECT subTSNI.TotalShippedNotInvoiced)
			ELSE (SELECT subTI.TotalInvoiced + subTSNI.TotalShippedNotInvoiced)
		END)
END AS TOTAL_SHIPPED,

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-05 : 12:27:50

This won't work ??
ISNULL(subTSNI.TotalShippedNotInvoiced,0)+ISNULL(subTI.TotalInvoiced,0)