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 |
|
fizgig
Starting Member
34 Posts |
 Posted - 2002-02-14 : 10:06:05
|
| Hi People!'Got an query like this;..., total = (SELECT sum(foo1.price) FROM foo1) + (SELECT price2 FROM foo2), ...If foo1 Has no records then it returns Null. Let's say price2 = 4. When doing a sum of the two subqueries it returns Null. How can i change it so it returns 4 ?? |
|
|
nlocklin
Yak Posting Veteran
69 Posts |
Posted - 2002-02-14 : 10:25:04
|
| Assuming that it is acceptable to represent the sum of foo1 as 0 if it has no records, you could use COALESCE:COALESCE((SELECT SUM(foo1.price) FROM foo1),0) AS foo1sumetc... which would say if the result of the select statement is NULL use 0 instead.Is that what you're looking for?--"I'm always doing that. I'm always messing up some mundane detail." |
 |
|
|
fizgig
Starting Member
34 Posts |
Posted - 2002-02-14 : 10:28:26
|
| Exactly what i needed! Tnx! |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-14 : 14:31:30
|
| you can also use Isnull function isnull(foo1.price,0).HTH--------------------------------------------------------------"Happiness is not something you experience, it's something you remember." |
|
|
|
Spyder
SQLTeam Author
75 Posts |
Posted - 2002-02-16 : 16:22:34
|
You can also turn off this behavior with an in-line SET statement:SET CONCAT_NULL_YIELDS_NULL OFF..., total = (SELECT sum(foo1.price) FROM foo1) + (SELECT price2 FROM foo2), ...SET CONCAT_NULL_YIELDS_NULL ON |
|
|
|
|
|
|
Null + 0 = Null problem