Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    3

    Unanswered: Query returns null when there is a value for some rows

    I have a query that when I run it returns the correct number of results. The
    problem is that on some, all but three fields are blank. The fields that have
    information are claim #, Action Code, and Date. I have verified that there is
    data in those fields for each of those records.

    I can not post the complete results as there is personal customer information.
    Here is one that did return data that I did verify exists. I copied and pasted,
    sorry that they did not line up.

    Claim # Action Code Last Name First Name Date Home Phone Work Phone Extension
    Other Phone Loss Date Policy # Producer Agent Name
    309298 H10Y NULL NULL 2009-08-17 18:42:41.000 NULL NULL NULL NULL NULL NULL NULL
    NULL


    Here is my code.

    SELECT DISTINCT
    Apacs_Reporting.dbo.tblClaimActions.ClaimNumber AS [Claim
    #], Apacs_Reporting.dbo.tblClaimActions.ActionCode AS [Action Code],
    dbo.tblMasterPolicy.LastName AS [Last Name],
    dbo.tblMasterPolicy.FirstName AS [First Name],
    Apacs_Reporting.dbo.tblClaimActions.CreatedOn AS Date,
    Apacs_Reporting.dbo.tblClaim.BHomePhone AS [Home Phone],
    Apacs_Reporting.dbo.tblClaim.BPhone AS [Work Phone],
    Apacs_Reporting.dbo.tblClaim.BPhExt AS Extension,
    Apacs_Reporting.dbo.tblClaim.BOtherPhone AS [Other Phone],
    Apacs_Reporting.dbo.tblClaim.LossDate AS [Loss Date],
    dbo.tblMasterPolicy.PolicyNumber AS [Policy #],
    dbo.tblProducers.ProducerName AS Producer, dbo.tblAgents.AgentName AS [Agent
    Name]
    FROM dbo.tblMasterPolicy INNER JOIN
    dbo.tblValidProducts ON dbo.tblMasterPolicy.ValidProductID
    = dbo.tblValidProducts.ValidProductID INNER JOIN
    dbo.tblProducers ON dbo.tblValidProducts.ProducerID =
    dbo.tblProducers.ProducerID INNER JOIN
    dbo.tblProducerGroupAgent ON
    dbo.tblProducers.ProducerGroupID = dbo.tblProducerGroupAgent.ProducerGroupID
    INNER JOIN
    dbo.tblAgents ON dbo.tblProducerGroupAgent.AgentID =
    dbo.tblAgents.AgentID LEFT OUTER JOIN
    Apacs_Reporting.dbo.tblClaim ON
    dbo.tblMasterPolicy.PolicyID = Apacs_Reporting.dbo.tblClaim.PolicyID FULL OUTER
    JOIN
    Apacs_Reporting.dbo.tblClaimActions ON
    Apacs_Reporting.dbo.tblClaim.ClaimNumber =
    Apacs_Reporting.dbo.tblClaimActions.ClaimNumber
    WHERE (Apacs_Reporting.dbo.tblClaimActions.CreatedOn >= GETDATE() - 7) AND
    (Apacs_Reporting.dbo.tblClaimActions.ActionCode = 'h10y')
    ORDER BY Date

    Thanks for any help.
    Ted

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    dbo.tblMasterPolicy.FirstName AS [First Name],
    Apacs_Reporting.dbo.tblClaimActions.CreatedOn AS Date,
    Apacs_Reporting.dbo.tblClaim.BHomePhone AS [Home Phone],
    You use [] everywhere to enclose your column names. Why not also with the never-use-reserved-words-for-column-names "Date"-column?

    I noticed a LEFT OUTER JOIN and a FULL OUTER JOIN. Look for your problem there. dbo.tblMasterPolicy will have records without corresponding dbo.tblClaimActions record.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2006
    Posts
    3
    I should have had brackets around the column titles. I am usually very good at doing that.

    as far as the full outer join, if I don't have either a full or right outer join, I do not get the correct amount of results. I did verify that the records returned should be included as they are claims that do have the action code H10Y entered for them. They do have data in the columns that are returning null.

    Changing the left outer to inner, right outer or full has no effect on the results.

  4. #4
    Join Date
    Apr 2006
    Posts
    3
    I am working on it again trying some ideas. I removed all the coding required to get th last two columns and it ran perfect. The same number of results with all the information.

    SELECT DISTINCT
    Apacs_Reporting.dbo.tblClaimActions.ClaimNumber AS [Claim #]
    , Apacs_Reporting.dbo.tblClaimActions.ActionCode AS [Action Code]
    , Apacs_Reporting.dbo.tblMasterPolicy.LastName AS [Last Name]
    , Apacs_Reporting.dbo.tblMasterPolicy.FirstName AS [First Name]
    , Apacs_Reporting.dbo.tblClaimActions.CreatedOn AS [Date]
    , Apacs_Reporting.dbo.tblClaim.BHomePhone AS [Home Phone]
    , Apacs_Reporting.dbo.tblClaim.BPhone AS [Work Phone]
    , Apacs_Reporting.dbo.tblClaim.BPhExt AS [Extension]
    , Apacs_Reporting.dbo.tblClaim.BOtherPhone AS [Other Phone]
    , Apacs_Reporting.dbo.tblClaim.LossDate AS [Loss Date]
    , Apacs_Reporting.dbo.tblMasterPolicy.PolicyNumber AS [Policy #]
    FROM Apacs_Reporting.dbo.tblMasterPolicy INNER JOIN
    Apacs_Reporting.dbo.tblClaim ON Apacs_Reporting.dbo.tblMasterPolicy.PolicyID = Apacs_Reporting.dbo.tblClaim.PolicyID FULL OUTER JOIN
    Apacs_Reporting.dbo.tblClaimActions ON Apacs_Reporting.dbo.tblClaim.ClaimNumber = Apacs_Reporting.dbo.tblClaimActions.ClaimNumber
    WHERE (Apacs_Reporting.dbo.tblClaimActions.CreatedOn >= GETDATE() - 7) AND (Apacs_Reporting.dbo.tblClaimActions.ActionCode = 'h10y')
    ORDER BY [Date]

    Thus it is due to something with the last two columns from my origional coding.

    Now this is a challenge.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •