CODEWITHSUNDEEP
sql
Jude
Jude

Reputation: 87

Nested conditions in sql

I have the where condition in the sql:

WHERE
  ( Spectrum.access.dim_member.centene_ind = 0  )
  AND  
  (
   Spectrum.access.Client_List_Groups.Group_Name  IN  ( 'Centene Health Plan     Book of Business'  )
   AND
   Spectrum.access.dim_member.referral_route  IN  ( 'Claims Data'  )
   AND
   ***(
    Spectrum.access.fact_task_metrics.task  =  'Conduct IHA'
    AND
Spectrum.access.fact_task_metrics.created_by_name  <>  'BMU, BMU'
AND
Spectrum.access.fact_task_metrics.created_date  BETWEEN  '01/01/2015 00:0:0'  AND  '06/30/2015 00:0:0'
   )***
AND
***(
Spectrum.access.fact_outreach_metrics.outreach_type  IN  ( 'Conduct IHA'  )
AND
(
 Spectrum.dbo.ufnTruncDate(Spectrum.access.fact_outreach_metrics.metric_date)  >=  Spectrum.access.fact_task_metrics.metric_date
 OR
 Spectrum.access.fact_outreach_metrics.metric_date  >=  Spectrum.access.fact_task_metrics.created_date
)
)***
AND
Spectrum.access.fact_outreach_metrics.episode_seq  =  1
AND
Spectrum.access.dim_member.reinstated_date  Is Null  
)

I have marked two of the conditions in the above code.

The 1st condition have 2 AND operators.

The 2nd condition has an AND and an OR operator.

Question 1: Does removing the outer brackets "(" in the 1st condition impact the results?

Question 2: Does removing the outer brackets "(" in the 2nd condition impact the results?

After removing the outer bracket the filters will look like:

   Spectrum.access.dim_member.referral_route  IN  ( 'Claims Data'  )
   AND

    Spectrum.access.fact_task_metrics.task  =  'Conduct IHA'
    AND
Spectrum.access.fact_task_metrics.created_by_name  <>  'BMU, BMU'
AND
Spectrum.access.fact_task_metrics.created_date  BETWEEN  '01/01/2015 00:0:0'  AND  '06/30/2015 00:0:0'

AND

Spectrum.access.fact_outreach_metrics.outreach_type  IN  ( 'Conduct IHA'  )
AND
(
 Spectrum.dbo.ufnTruncDate(Spectrum.access.fact_outreach_metrics.metric_date)  >=  Spectrum.access.fact_task_metrics.metric_date
 OR
 Spectrum.access.fact_outreach_metrics.metric_date  >=  Spectrum.access.fact_task_metrics.created_date
)

AND
Spectrum.access.fact_outreach_metrics.episode_seq  =  1

Appreciate your help.

Regards, Jude

Upvotes: 1

Views: 94

Answers (2)

Kaitebug
Kaitebug

Reputation: 504

Technically, you should be able to safely remove the parenthesis in question for both of your examples. With the AND statement, you are adding all of your conditions together to be one large condition. When using the OR clause, you should carefully place the parenthesis so that the groups are properly segmented.

Take the following examples into consideration:

a) where y = 1 AND n = 2 AND x = 3 or x = 5
b) where y = 1 AND n = 2 AND (x = 3 or x = 5)
c) where (y = 1 AND n = 2 AND x = 3) or x = 5

In example A, the intended outcome is unclear.

In example B, the intended outcome states that all of the conditions must be met and X can be either 3 or 5.

In example C, the intended outcome states that either Y=1, N=2 and X=3 OR x=5. As long as X = 5, it doesn't matter what Y and N equal.

Upvotes: 1

Jason W
Jason W

Reputation: 13179

Order of operations dictate that AND will be processed before OR when these expressions are evaluated within a parenthesis set. 

WHERE (A AND B) OR (C AND D)

Is equivalent to:

WHERE A AND B OR C AND D

But the example below:

WHERE (A OR B) AND (C OR D)

Is not equivalent to:

WHERE A OR B AND C OR D

Which really becomes:

WHERE A OR (B AND C) OR D

Upvotes: 2

Related Questions