Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Some Data Coming as NULL in a VIew Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 5:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 11:18 AM
Points: 56, Visits: 127
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


Post #1566881
Posted Friday, May 2, 2014 12:16 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 16, 2014 9:28 AM
Points: 512, Visits: 433
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.
Post #1566930
Posted Friday, May 2, 2014 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1566949
Posted Wednesday, May 21, 2014 6:03 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:20 PM
Points: 373, Visits: 909
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?
Post #1573382
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse