On Behalf Of Maya_Zakry

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?

NO!

But if you are trying to sum up different columns depening on some status column, try thisselectcol1,
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
fromtblone
group bycol1,
col2
order bycol1,
col2

Peter Larsson
Helsingborg, Sweden