Query Not Like returning odd results

Question

Simple Query that I am stumped on. I am trying to filter out jobs that end in 4120and so forth. When I run this query

select Job.Job, Job.Status from Job 
    Where(Job.Job Not Like '%4120'
          And Job.Job Not Like '%4235'
          And Job.Job Not Like '%4236'
          And Job.Job Not Like '%5910'
          And Status = 'Active' OR Status = 'Complete') 
    Order By Job.Job

I Get these results

    Job         Status
    01-19-4120  Complete
    01-19-4235  Complete
    01-19-5910  Complete
    02-19-4120  Complete
    02-19-4235  Complete
    02-19-4236  Complete
    02-19-5910  Complete
    03-07-4120  Complete
    03-19-4120  Complete
    03-19-4235  Complete
    03-19-5910  Complete
    04-19-4120  Complete
    04-19-4160  Complete

Is there something wrong with my query?

Answer 1

You have a logical prescendence problem. I highly suspect that you should be surrounding the ORed conditions with parentheses, as follows:

Job.Job Not Like '%4120'
And Job.Job Not Like '%4235'
And Job.Job Not Like '%4236'
And Job.Job Not Like '%5910'
And (Status = 'Active' OR Status = 'Complete') 

Explanation: since logical operator OR has lower prescendence than AND, your WHERE conditions, as they are, are actually equivalent to:

(
    Job.Job Not Like '%4120'
    And Job.Job Not Like '%4235'
    And Job.Job Not Like '%4236'
    And Job.Job Not Like '%5910'
    And Status = 'Active' 
) OR Status = 'Complete' 

This allows any record whose Status is 'Complete', regardless of the value of Job, which is not what you want.

Also, please note that it should be possible to simplify these conditions by using a string function instead of Not Likes and an in condition instead of Or. Assuming that your RDBMS supports right():

right(Job.Job, 4) not in ('4120', '4235', '4236', '5910') 
and Status in ('Complete', 'Active')