In where clause for my query if I include condition in where clause such as
where 
      T2.Comments not like '%ABC%' 
  and T2.Comments not like '%xyz%'
it also filters out blanks/null comments. I do not want it to filter out null or blanks.
What is another way to filter out eg. ABC and xyz and also not exclude nulls/blanks?
rest of where caluse.. (after adding bottom two condition I do not get any nulls.
Where
    T1.OUT_NO = T2.OUT_NO 
AND T3.OUT_NO = T1.OUT_NO 
AND CAUSE_CAP.CAUSE_NO NOT IN (1,3,5,7,9) 
AND ("T1"."TIME_STAMP">=TO_DATE ('01-04-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "T1"."TIME_STAMP"<TO_DATE ('06-04-2013 23:59:59', 'DD-MM-YYYY HH24:MI:SS')) 
AND NOT (CAUSE_CAP.CAUSE_NO = 13 AND START_TABLE.TABLE_NO = 83)
AND T2.Comments not like '%ABC%'
AND T2.Comments not like '%XYZ%'
                just add NULL as a specific case. [edited to show how to generalise this approach to OPs more complex query]
Where
    T1.OUT_NO = T2.OUT_NO 
AND T3.OUT_NO = T1.OUT_NO 
AND CAUSE_CAP.CAUSE_NO NOT IN (1,3,5,7,9) 
AND ("T1"."TIME_STAMP">=TO_DATE ('01-04-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "T1"."TIME_STAMP"<TO_DATE ('06-04-2013 23:59:59', 'DD-MM-YYYY HH24:MI:SS')) 
AND NOT (CAUSE_CAP.CAUSE_NO = 13 AND START_TABLE.TABLE_NO = 83)
AND (T2.Comments IS NULL OR 
        (T2.Comments not like '%ABC%'
         AND T2.Comments not like '%XYZ%')
    )
                        When you have a condition (column LIKE 'somecode') then all NULL values are excluded from the results. Only rows that have not null can result in the condition being true. So, I think you want:
where  
      (   (   T2.Comments not like '%ABC%' 
          and T2.Comments not like '%xyz%'
          )
      or  T2.Comments IS NULL 
      )
AND has higher precedence than OR so the inner parentheses are not needed, only added for clarity (as @horse_with_no_name's comment). The outer ones are not needed either if you have only this condition.
In other DBMS, you would also need or T2.Comments = '' but not in Oracle, because NULL and the empty string are the same thing for char columns.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With