Some Data Coming as NULL in a VIew

  • tinausa

    SSCrazy

    Points: 2657

    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

    Hall of Fame

    Points: 3054

    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

    SSC Guru

    Points: 186045

    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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • MMartin1

    One Orange Chip

    Points: 27488

    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 [/url]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply