Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Some Data Coming as NULL in a VIew


Some Data Coming as NULL in a VIew

Author
Message
tinausa
tinausa
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 172
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
Bhaskar.Shetty
Bhaskar.Shetty
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 509
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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9025 Visits: 19036
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
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 2015
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?

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search