Using Case Statement when there is no entries in table

Question

I want to fulfill below condition for a field in the left outer join

If the field1 = Open and field2 ne 3 then I need 'OPEN' else 'CLOSE' and there is no entry in the table then i need 'NEUTRAL'

this is the coding written but it returns either OPEN and CLOSE but not neutral if table3 doesnt have entry based on join condition which is given below. Kindly help.

   SELECT coalesce ( ( 
       case 
           when tab3.field1 = 'Open' and tab3.field2 <> '3' then 'OPEN'
           else 'CLOSE'
       end
       ),'NEUTRAL') as STATUS  
   FROM table1 AS tab1    
   LEFT OUTER JOIN table3 AS tab3 
                on tab1.material = tab3.material

Answer 1

You wanting something like this:

case
    when tab3.material is null then 'NEUTRAL'
    when tab3.field1  = 'Open' and tab3.field2 <> '3' then 'OPEN'
    else 'CLOSE'
end as Status