CODEWITHSUNDEEP
sql-servert-sql
Zach M.
Zach M.

Reputation: 1194

WHERE clause in SQL ignoring OR

I have a stored procedure that is calculating the number of documents I have that are not in complete (1000) or canceled (1100). When I have just one of those conditionals it counts the number correctly but once I add the or, it simply grabs everything ignoring any of the logic. There must be some fundamental thing i'm missing with SQL here

    SELECT 
       [PartnerCoId] as DisplayID
      ,[PartnerCompanyName] as DisplayName
      ,Count(*) as DocumentTotal
  FROM vwDocuments 
  where coid = @inputCoid and DocumentType = 'order' 
  and Status <> 1100 
  and PartnerCoId <> @inputCoid
  group by 
        [PartnerCoId]
       ,[PartnerCompanyName]
union all
SELECT [CoId] as DisplayID
      ,[CompanyName] as DisplayName      
      ,Count(*) as DocumentTotal
  FROM vwDocuments 
  where PartnerCoId = @inputCoid and DocumentType = 'order' 
  and Status <> 1100 
  and CoId <> @inputCoid
  group by [CoId]
      ,[CompanyName]            
    order by [DisplayName]

This will return the number of documents not in canceled status. If I change the 1100 to 1000 it returns the number of documents not in complete status. Once I update the query to:

and (Status <> 1100 or Status <> 1000)

It breaks the logic.

Thoughts? I have tried quite a number of different combinations of query logic and cannot straighten this out.

Upvotes: 0

Views: 157

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

Let's simplify and think about your logic.

DECLARE @i INT = 1;

IF (@i <> 1 OR @i <> 2)
  PRINT 'true';
ELSE
  PRINT 'false';

Can you provide a value for @i that generates false? I can't think of one. Think about it:

  • if @i = 1, then @i <> 1 is false, but @i <> 2 is true. Since only one of these conditions has to be true for the whole condition to be true, the result is true.
  • if @i = 2, then @i <> 1 is true, but @i <> 2 is false. Since only one of these conditions has to be true for the whole condition to be true, the result is true.
  • if @i is any other value outside of 1 and 2, then both conditions are true.

As the other answers demonstrate, the way to fix this is to change OR to AND, or use NOT IN (though I like NOT IN less because when you are checking a column, and the column is NULLable, the results surprise and confuse most people - and I prefer to program consistently instead of having to be aware of the cases where something works and the cases where it really, really doesn't).

Upvotes: 0

fshaw1
fshaw1

Reputation: 366

I think your logic may be a little bit off. Saying (NOT complete or NOT cancelled) will return all of your documents because all rows will fit this criteria.

Take a document with status of 1100. This will be returned in the query because it evaluates to TRUE on half of the OR statement.

Try replacing that line with 

AND Status NOT IN (1000,1100)

This should return only documents that are neither completed or cancelled. Hope this helps!

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

Rather than wrestle with boolean logic, use not in:

and Status not in (1100, 1000)

It's easier to read and understand, because it's practically English, and because it's all in one statement you don't need brackets around it either.

Upvotes: 6

Roger Rowland
Roger Rowland

Reputation: 26259

If I understand you correctly, you want everything where Status is neither 1100 nor 1000.

If so, then you need this:

and (Status <> 1100 and Status <> 1000)

If you use or, then a Status of 1100 will pass the test because it is <> 1000.

Upvotes: 4

Related Questions