May 1, 2014 at 5:57 pm
User error ----
--------------------------------------
I have the following view.
The column prod_Master.M2_Pct is defined as float.
Yet for some of the rows, the value in [% of Total Target] comes up as null even though there is a number in prod.Measure column. There is valid non-null data in prod_Master.M2_Pct.
Any thoughts on why that would happen? I tried LTRIM(RTRIM(prod.Measure)), but no change.
Use ProdDB
SELECT TOP (100) PERCENT dbo.prod.ProdNo AS [Prod No], dbo.prod.ProdName AS [Prod Name],
CASE WHEN dbo.prod.Measure = 'P1' THEN dbo.prod_Master.P1_Pct
WHEN dbo.prod.Measure = 'P2' THEN dbo.prod_Master.P2_Pct
WHEN dbo.prod.Measure = 'P3' THEN dbo.prod_Master.P3_Pct
WHEN dbo.prod.Measure = 'P4' THEN dbo.prod_Master.P4_Pct
END AS [% of Total Target]
FROM dbo.prod LEFT OUTER JOIN dbo.prod_Master ON
dbo.prod.RoleID = dbo.prod_Master.RoleID AND
dbo.prod.ProdNo = dbo.prod_Master.ProdNo
WHERE (dbo.prod.Measure = 'P2' OR
dbo.prod.Measure = 'P4') AND
(dbo.prod.ProdRole NOT IN ('catA', 'catB'))
GROUP BY dbo.prod.ProdNo, dbo.prod.Measure, dbo.prod.ProdName, dbo.prod.ProdRole,
CASE WHEN dbo.prod.Measure = 'P1' THEN dbo.prod_Master.P1_Pct
WHEN dbo.prod.Measure = 'P2' THEN dbo.prod_Master.P2_Pct
WHEN dbo.prod.Measure = 'P3' THEN dbo.prod_Master.P3_Pct
WHEN dbo.prod.Measure = 'P4' THEN dbo.prod_Master.P4_Pct
END
:hehe:
May 2, 2014 at 12:16 am
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.
May 2, 2014 at 2:06 am
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
May 21, 2014 at 6:03 pm
Perhaps the OP simply forgot to include the other cases in the where clause
ChrisM , If I can borrow your code...
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 ('P1','P2','P3','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
Is this what you wanted?
----------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply