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%')
)