quote:

---

Originally posted by maya_zakry

hello, can someone fix my multiple case statement..

SELECT col1, col2 from tblOne

CASE
WHEN (substring(d.StorageStatus,1,1)='H') THEN
Sum(d.HoldQty)
WHEN (substring(d.StorageStatus,1,1)='Q') THEN
Sum(d.QCQty)
ELSE
sum(ISNULL(d.OriginQty,0))-sum(ISNULL(d.HoldQty,0))- sum(ISNULL (d.QCQty,0))- sum(ISNULL(d.BookQty,0))- sum(ISNULL(p.PickQty,0)) AS BalQty END

AS BalQty

WHERE blablah
is this multiple case allowed?

---

select		col1,
		col2,
		sum(case 
			when d.storagestatus like 'h%' then isnull(d.holdqty, 0)
			when d.storagestatus like 'q%' then isnull(d.qcqty, 0)
			else isnull(d.originqty, 0) - isnull(d.holdqty, 0) - isnull (d.qcqty, 0) - isnull(d.bookqty, 0) - isnull(p.pickqty, 0)
		end) as qty
from		tblone
group by	col1,
		col2
order by	col1,
		col2