WHERE condition being ignored

  • Hello, I have the below WHERE code and it seems to be ignoring the Policy Status of 10 and 15 and it's returning many more statuses.


    WHERE PD.PolicyStatus IN ( SELECT ParameterId
           FROM Company.Schema.GeneralParameter
           WHERE ParameterName='PolicyStatus'
           AND ParameterAdditionalId IN (10,15)
           )

    Full WHERE Code

    WHERE PD.PolicyStatus IN ( SELECT ParameterId
           FROM Company.Schema.GeneralParameter
           WHERE ParameterName='PolicyStatus'
           AND ParameterAdditionalId IN (10,15)
           )
    AND PCov.CoverNumber IS NOT NULL OR CCC.CoverNumber = 0
    AND PD.InsuranceCompany = @InsuranceCompany OR @InsuranceCompany = 0
    AND PD.ProductNumber = @ProductNumber OR @ProductNumber = 0

    I obviously do not want any of the conditions to be ignored. Any ideas why?
    Thanks.

  • NikosV - Wednesday, October 10, 2018 10:48 PM

    Hello, I have the below WHERE code and it seems to be ignoring the Policy Status of 10 and 15 and it's returning many more statuses.


    WHERE PD.PolicyStatus IN ( SELECT ParameterId
           FROM Company.Schema.GeneralParameter
           WHERE ParameterName='PolicyStatus'
           AND ParameterAdditionalId IN (10,15)
           )

    Full WHERE Code

    WHERE PD.PolicyStatus IN ( SELECT ParameterId
           FROM Company.Schema.GeneralParameter
           WHERE ParameterName='PolicyStatus'
           AND ParameterAdditionalId IN (10,15)
           )
    AND PCov.CoverNumber IS NOT NULL OR CCC.CoverNumber = 0
    AND PD.InsuranceCompany = @InsuranceCompany OR @InsuranceCompany = 0
    AND PD.ProductNumber = @ProductNumber OR @ProductNumber = 0

    I obviously do not want any of the conditions to be ignored. Any ideas why?
    Thanks.

    This is most likely due to the Operator Precedence.  SQL will process all of the ANDs before the ORs.
    You probably want to use something like AND (PD.InsuranceCompany = @InsuranceCompany OR @InsuranceCompany = 0) but note, these queries will result in table scans.

  • NikosV - Wednesday, October 10, 2018 10:48 PM

    Hello, I have the below WHERE code and it seems to be ignoring the Policy Status of 10 and 15 and it's returning many more statuses.


    WHERE PD.PolicyStatus IN ( SELECT ParameterId
           FROM Company.Schema.GeneralParameter
           WHERE ParameterName='PolicyStatus'
           AND ParameterAdditionalId IN (10,15)
           )

    Full WHERE Code

    WHERE PD.PolicyStatus IN ( SELECT ParameterId
           FROM Company.Schema.GeneralParameter
           WHERE ParameterName='PolicyStatus'
           AND ParameterAdditionalId IN (10,15)
           )
    AND PCov.CoverNumber IS NOT NULL OR CCC.CoverNumber = 0
    AND PD.InsuranceCompany = @InsuranceCompany OR @InsuranceCompany = 0
    AND PD.ProductNumber = @ProductNumber OR @ProductNumber = 0

    I obviously do not want any of the conditions to be ignored. Any ideas why?
    Thanks.

    WHERE PD.PolicyStatus IN ( SELECT ParameterId
       FROM Company.Schema.GeneralParameter
       WHERE ParameterName='PolicyStatus'
       AND ParameterAdditionalId IN (10,15)
       )
    AND PCov.CoverNumber IS NOT NULL OR CCC.CoverNumber = 0
    AND PD.InsuranceCompany = @InsuranceCompany OR @InsuranceCompany = 0
    AND PD.ProductNumber = @ProductNumber OR @ProductNumber = 0

    Is exactly the same as:
    WHERE (PD.PolicyStatus IN (SELECT ParameterId
                                 FROM Company.Schema.GeneralParameter
                                WHERE ParameterName='PolicyStatus'
                                  AND ParameterAdditionalId IN (10,15))
            AND PCov.CoverNumber IS NOT NULL
         )
      OR (CCC.CoverNumber = 0 AND PD.InsuranceCompany = @InsuranceCompany)
      OR (@InsuranceCompany = 0 AND PD.ProductNumber = @ProductNumber)
      OR (@ProductNumber = 0)

  • Aside from needing brackets, you might also want to read this:
    https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/

    That query's going to perform very badly.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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