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

Siddharth Bhardwaj 0
Viewed 82

I have the following data

Id Indicator_1 Indicator_2 Order_Number
1 X L 123
2 X null 123
3 X null null
4 null null null
5 null null 456

The logic should be hide if:

  1. IF indicator_1 = X AND indicator_2 = L, then hide the record
  2. if indicator_1= X and Order_Number is null, then hide the record

I want to exclude rows 1 and 3 from the table

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 ) 

      )
oracle database sql
1 Answers

Your condition can be simplified to:

WHERE NOT (
  COALESCE(indicator_1, ' ') = 'X' 
  AND 
  (COALESCE(indicator_2, ' ') = 'L' OR Order_Number IS NULL)
)

or:

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

or:

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

See the demo.

forpas0
Related