Convert Null to zero or empty?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Convert Null to zero or empty??

Author

Topic

jeanny88
Starting Member

2 Posts

Posted - 2009-03-28 : 16:17:09

Here is my table
Cart --> CartID, FoodID, FoodID2, FoodID3, csessionID, OrderID
Food --> FoodID, Name, Calories, UnitPrice

In the cart, the data of FoodID2 and FoodID3 might be Null which mean i din put value on it. So in the select query below, i cannot sum the calories which have Null value, also the Name of the food which have Null value. Means row that have Null value, either calculation or combine the name; the final output will be NULL.

So, any suggestion how i m going to fix this?? thx..

Here is my query...


SELECT        cart.CartID, f1.Calories, SUM(f1.Calories + f2.Calories) AS Total, f2.Name, f1.Name + ' and ' + f2.Name + '  with  ' + f3.Name AS Meals
FROM            Cart AS cart LEFT OUTER JOIN
                             (SELECT        FoodID, Name, Unitprice, Description, Categories, Calories, Fats, Proteins, Carbohydrates, pics
                               FROM            Food) AS f1 ON cart.FoodID = f1.FoodID LEFT OUTER JOIN
                             (SELECT        FoodID, Name, Unitprice, Description, Categories, Calories, Fats, Proteins, Carbohydrates, pics
                               FROM            Food AS Food_2) AS f2 ON cart.FoodID2 = f2.FoodID LEFT OUTER JOIN
                             (SELECT        FoodID, Name, Unitprice, Description, Categories, Calories, Fats, Proteins, Carbohydrates, pics
                               FROM            Food AS Food_1) AS f3 ON cart.FoodID3 = f3.FoodID
GROUP BY cart.CartID, f1.Calories, f2.Calories, f3.Calories, f1.Name, f2.Name, f3.Name

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-28 : 16:22:34

SUM(coalesce(f1.Calories, 0) + coalesce(f2.Calories, 0))

E 12°55'05.63"
N 56°04'39.26"

jeanny88
Starting Member

2 Posts

Posted - 2009-03-28 : 23:07:03

Thanx...It's working..^^ And for the string, i m using COALESCE(f1.Name,'')... It's really help me alot... thx so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources