October 10, 2018 at 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.
October 10, 2018 at 11:10 pm
NikosV - Wednesday, October 10, 2018 10:48 PMHello, 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 = 0I 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.
October 12, 2018 at 6:23 pm
NikosV - Wednesday, October 10, 2018 10:48 PMHello, 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 = 0I 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)
October 13, 2018 at 11:49 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply