How to give priority to filters in "WHERE" condition in oracle?-CodePudding

I have the following data

Id	Indicator_1	Indicator_2	Order_Number
1	X	L	123
2	X	null	123
3	X	null	null

I don't want to select rows where Condition 1: Indicator_1 = X AND Indicator_2 = L Condition 2: Indicator_1 = X AND Order_Number is null

I want to give priority to the first condition that if a row has Indicator_1 = X AND Indicator_2 = L then don't select it, if this is not the case then check whether Indicator_1 = X and Order number is null then don't select this particular row.

I tried the following query but this doesn't seems to be working

SELECT a.id,
       a.Indicator_1,
       a.Indicator_2,
       a.Order_Number,
       a.*
FROM   dummy a
WHERE  ( ( a.indicator_1 <> 'X'AND a.indicator_2 <> 'L' )
          OR 
         ( a.indicator_1 <> 'X'AND a.Order_Number IS NOT NULL ) 
       )

CodePudding user response：

We can write the logic as:

SELECT a.*
FROM dummy a
WHERE NOT (a.indicator_1 = 'X' AND a.indicator_2 = 'L') AND
      NOT (a.indicator_1 = 'X' AND a.Order_Number IS NULL);

By DeMorgan's Laws, we could rewrite the above to:

SELECT a.*
FROM dummy a
WHERE (a.indicator_1 != 'X' OR a.indicator_2 != 'L') AND
      (a.indicator_1 != 'X' OR a.Order_Number IS NOT NULL);

But, I would probably go with the first version, as it clearly reveals the original logic.

CodePudding user response：

Your condition can be simplified to:

WHERE NOT (a.indicator_1 = 'X' AND (a.indicator_2 = 'L' OR a.Order_Number IS NULL))

or:

WHERE a.indicator_1 <> 'X' OR NOT (a.indicator_2 = 'L' OR a.Order_Number IS NULL)

or:

WHERE a.indicator_1 <> 'X' OR (a.indicator_2 <> 'L' AND a.Order_Number IS NOT NULL)