11

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.

14

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'. – Alex Tennant May 29 '14 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 '14 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.