I am attempting the following query but I seem to be missing something obvious:

//Gets a list of the contact Ids which dont have a classification type or dont have a specified type 

List<cx__Contact_Classification__c> classification = 
  [SELECT 
     cx__Contact__c 
   FROM 
     cx__Contact_Classification__c 
   WHERE 
     cx__Contact__c IN :contactIdSet
     AND cx__Classification_Type__c NOT IN 
       (SELECT 
          id 
        FROM 
          cx__Classification_Type__c 
        WHERE 
          Name = 'blah' 
          OR Name = 'foo' 
          OR Name = 'foobar'
          OR Name = 'foobar1' 
          OR Name = 'foobar2' 
          OR Name = 'foobar3') 
    OR cx__Classification_Type__c = null];

At the final OR I get the

Expecting right square bracket, found 'OR'

Any help much appreciated.

Attribution to: Jim

Possible Suggestion/Solution #1

SOQL has a difficult time distinguishing between ANDs and ORs when used on the same line. I'd suggest rewriting your query a bit to say CX_Classification_Type__c = null OR (Cx_Classification_Type__c in :contactIdSet AND Cx_Classification_Type__c NOT IN (..))

Attribution to: James Loghry

Possible Suggestion/Solution #2

It sounds like it is expecting the query to end at the end of your NOT IN clause. Just delete the OR cx_Classification_Type__c = null clause. ;) (Just kidding...sort of)

List classification = [
   SELECT cx_Contact_c 
   FROM cx__Contact_Classification_c 
   WHERE cx_Contact_c IN :contactIdSet 
      AND cx_Classification_Type_c NOT IN (SELECT id 
                                           FROM cx_Classification_Type_c 
                                           WHERE Name = 'blah' 
                                           OR Name = 'foo' 
                                           OR Name = 'foobar' 
                                           OR Name = 'foobar1' 
                                           OR Name = 'foobar2' 
                                           OR Name = 'foobar3') 
      OR cx_Classification_Type__c = null
];

For those of us without your custom objects, here is an example using Account and Opportunity.

The following gives the 'Semi join sub-selects are not allowed with the 'OR' operator'

SELECT Id, Name
From Opportunity
Where StageName = 'Closed'
Or AccountId In(Select Id From Account)

Whereas, the following gives the 'unexpected token: Or'

SELECT Id, Name
From Opportunity
Where StageName = 'Closed'
And AccountId In(Select Id From Account)
Or StageName = 'Open'

There are all kinds of limitations to SOQL. Take a look at the SOQL documentation on semi-joins and anti-joins. About 1/3 of the way down there is a section titled Semi-Joins with IN and Anti-Joins with NOT IN.

From the Subquery limits section near the end of the SOQL documentation page:

• You cannot use subqueries in conjunction with OR.

The error message 'unexpected token: Or' is not a good one, in my opinion, but in any case the query you are trying to do cannot be done.

Attribution to: Peter Knolle
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/745