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.
| Author |
Topic |
|
hermandup
Starting Member
1 Post |
 Posted - 2006-05-05 : 06:54:20
|
| I've constructed a query that gets some records from a table. There's 3 fields in the table of type bigint and I want them added up. The query looks something like this:SELECT InvoiceNo, (DTAmount + KTAmount + Balance) AS Amount FROM InvoicesMy problem is that the addition ALWAYS results in NULL although some of the 3 fields definately have values. If I only select one of the 3 fields, it returns the correct value, but as soon as I try to add them up, it's as if the fields loose their values.In all of the records at least on of the fields has a NULL value. I've tried to CONVERT and CAST them to INTs, REALs and FLOATs, but nothing works.Any help??!! |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-05-05 : 07:12:45
|
| Try this...SELECT InvoiceNo, isnull(DTAmount, 0) + isnull(KTAmount, 0) + isnull(Balance, 0) AS Amount FROM InvoicesRyan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-05 : 07:24:47
|
"My problem is that the addition ALWAYS results in NULL although some of the 3 fields definately have values"When one of them is NULL, the result of the addition will be NULL. Try out Ryan's suggestion.
KH |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-05-05 : 10:29:26
|
| hermandup,Just to clarify, your problem was indeed the NULL. People tend to misunderstand NULL. NULL means an unknown (indeterminate) value. When you think of it that way, it makessense that your additions didn't work; how could "1 + 1 + (I don't know what it is)" produce any kind of result other than"I don't know what it is"?Ken |
|
|
|
|
|
|
|
|
Problem with simple addition of fields in query