'not like' in Oracle

Question

I have a case statement:

CASE WHEN (X_MED_CENTER IN ('X','S','W','X') 
        OR OUTSIDE_FAC IN ('X MEDICAL CENTER'))
       AND LD_NOTE IS NULL AND LOWER(PROBLEM_CMT) NOT like '%home%'  
     THEN 1 else 0 end as CONTRACT 

The issue is the LOWER(PROBLEM_CMT) NOT like '%home%' component as it appears to render the statement false when all the components are actually true -if I leave that part out -statement works fine.

Any suggestions welcomed to making it work properly or addressing if my logic is faulty.

Thanks.

Answer 1

NULL could be your problem - try using LOWER(nvl(PROBLEM_CMT,'x')) NOT like '%home%'

See this demontration

Create table test as
select 1 as id, 'Home' as PROBLEM_CMT from dual union all
select 2 as id, 'abroad' as PROBLEM_CMT from dual union all
select 3 as id, NULL as PROBLEM_CMT from dual;

select id from test
where LOWER(PROBLEM_CMT) NOT like '%home%';

        ID
----------
         2 

select id from test
where LOWER(nvl(PROBLEM_CMT,'x')) NOT like '%home%';   

        ID
----------
         2 
         3