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