SELECT 

CASE n.Description
	WHEN n.Description IS NULL THEN 'Unknown' 
	ELSE n.Description 
END


 AS [Item Description]

, COUNT(w.Qty) AS [Times Picked], SUM(w.Qty) AS [Volume Picked], w.ItemID, f.Locations
FROM tblWT w LEFT OUTER JOIN tblItem n ON w.ItemID= n.ItemID
			LEFT OUTER JOIN dbo.fReturnAllLocations(0) f ON n.ItemID= f.ItemID

GROUP BY n.Description, w.ItemID, f.Locations
ORDER BY [Times Picked] DESC, [Volume Picked] DESC 

quote:

---

Originally posted by anti-rich

hi all,

i am designing a query in sql2k5 for a report which will print out pickup stats for my warehouse. every item will have an item code (and items come in and out of the warehouse frequently, with the item being deleted off the warehouse's db when there are 0 stock), but because of the limited data i am being given, i wont always have a complete description of the item. i thought it would be wise to use a left outer join in my query, so a complete list of everything that is picked will show up on the stats, as well as how often the item (with missing data) is picked. so... here is what i have so far

SELECT 

CASE n.Description
	WHEN n.Description IS NULL THEN 'Unknown' 
	ELSE n.Description 
END


 AS [Item Description]

, COUNT(w.Qty) AS [Times Picked], SUM(w.Qty) AS [Volume Picked], w.ItemID, f.Locations
FROM tblWT w LEFT OUTER JOIN tblItem n ON w.ItemID= n.ItemID
			LEFT OUTER JOIN dbo.fReturnAllLocations(0) f ON n.ItemID= f.ItemID

GROUP BY n.Description, w.ItemID, f.Locations
ORDER BY [Times Picked] DESC, [Volume Picked] DESC 

ok, so the wierd thing of this problem is the case statement... it just refuses to evaluate, spitting out the error of 'Incorrect syntax near the keyword 'IS''. i dont understand why this is happening, as everything seems to be ok (to my eyes)... perhaps i am missing something. if someone could point it out i would be really grateful :)

cheers
adam

---

SELECT COALESCE(n.Description, 'UNKNOWN') AS [Item Description]