13

I currently have a SOQL statement which looks like so:

SELECT
   UPC__c
FROM
   Item_Status__c
WHERE
   Status__c in ('Void', 'OOS')
   AND Not_authorized__c = false
   AND (Response_Begin_Code__c = 'E' AND Response_Status_Code__c = 'NA')

But I actually want to select all records which DO NOT meet the criteria of the last condition. So I want all records where NOT (Response_Begin_Code__c = 'E' and Response_Status_Code__c = 'NA')

How do I write that in SOQL?

I tried using both NOT before the compound condition:
AND NOT (Response_Begin_Code__c = 'E' AND Response_Status_Code__c = 'NA')

and the negation operator !:
AND ! (Response_Begin_Code__c = 'E' AND Response_Status_Code__c = 'NA')

But both were not permitted.

Now I do know that logically I can rewrite my condition by negating each test and changing the AND to an OR like so:

AND (Response_Begin_Code__c != 'E' OR Response_Status_Code__c != 'NA')

but would prefer not to if I can avoid it, since I think it is less apparent what is being asked. I would prefer to be able to negate the entire compound condition.

2 Answers 2

18

Add parentheses around your NOT.

SELECT
   UPC__c
FROM
   Item_Status__c
WHERE
   Status__c in ('Void', 'OOS')
   AND Not_authorized__c = false
   AND (NOT (Response_Begin_Code__c = 'E' AND Response_Status_Code__c = 'NA'))
1

Simply turn:

AND (Response_Begin_Code__c = 'E' AND Response_Status_Code__c = 'NA')

into:

AND (Response_Begin_Code__c != 'E' OR Response_Status_Code__c != 'NA')
2
  • 3
    This isn't correct. In boolean algebra x ^ y = ¬(¬x v ¬y). So the reverse should be Response_Begin_Code__c != 'E' OR Response_Status_Code__c != 'NA'. May 29, 2014 at 15:04
  • I understand the boolean algebra, but I had thought he was actually asking something slightly different. I updated my answer to be correct.
    – dphil
    May 29, 2014 at 16:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .