How do you negate a compound condition in SOQL?

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.

Answer

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'))

Attribution
Source : Link , Question Author : PaulStock , Answer Author : Daniel Hoechst

Leave a Comment