Bhaskar.Shetty (5/2/2014)
Hi,the case condition for Measure_% is failling for the conditions applied, Add a Else section for the case where not a single condition matches True.
Thanks.
Unlikely - check the WHERE clause.
The most likely reason is the left join, where no matching rows exist in prod_Master. Test by changing the outer join to an inner join. Using table aliases to reduce code noise makes this a little more clear:
Use ProdDB
SELECT TOP (100) PERCENT
[Prod No] = p.ProdNo,
[Prod Name] = p.ProdName,
[% of Total Target] = CASE
--WHEN p.Measure = 'P1' THEN m.P1_Pct
WHEN p.Measure = 'P2' THEN m.P2_Pct
--WHEN p.Measure = 'P3' THEN m.P3_Pct
WHEN p.Measure = 'P4' THEN m.P4_Pct
END
FROM dbo.prod p
LEFT OUTER JOIN dbo.prod_Master m
ON p.RoleID = m.RoleID
AND p.ProdNo = m.ProdNo
WHERE p.Measure IN ('P2','P4')
AND p.ProdRole NOT IN ('catA', 'catB')
GROUP BY
p.ProdNo,
p.Measure,
p.ProdName,
p.ProdRole,
CASE
--WHEN p.Measure = 'P1' THEN m.P1_Pct
WHEN p.Measure = 'P2' THEN m.P2_Pct
--WHEN p.Measure = 'P3' THEN m.P3_Pct
WHEN p.Measure = 'P4' THEN m.P4_Pct
END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden