0

I am attempting to test a query being used in an external application with salesforce integration where this query was generating a 'malformed query' error but I am running into an issue where the query is causing the error in the question title to be thrown in the dev console to be thrown.

Here is the query:

select field1,field2 from Account where not field1 like '%GEN' and field2 != null

Is it not possible to use multiple where conditions with 'like' in SOQL? If so are there any workarounds other than using two queries for this use case (not practical for the external application)?

Some background info on the external application in question, it has a Salesforce plugin which uses Salesforce SOQL query language to generate API calls to fetch data into the running job.

1

I was already writing this up as an answer, so I'll post it even though it's mostly explained in the comments. SObject Query Language doesn't know whether to interpret your WHERE clause as:

NOT (Field1 LIKE '%GEN') AND Field2 != null

or

NOT (Field1 LIKE '%GEN' AND Field2 != null)

Now, there are two ways you can remove this ambiguity. I recommend the first below as it is much more clear in my opinion.

  1. Use Field1 NOT LIKE instead of NOT Field1 LIKE:

    Field1 NOT LIKE '%GEN' AND Field2 != null
    
  2. Wrap the relevant clause in parenthises:

    NOT (Field1 LIKE '%GEN') AND Field2 != null
    
| improve this answer | |
  • #2 worked. #1 keeps giving me 'Parse Error' in the developer console. – Geowil Dec 16 '16 at 5:58
0

Answering my own post for others that might have a similar issue. I figured it out as soon as I posted my question. You must encapsulate the 'like' command in parenteses to separate it from the rest of the query:

select field1,field2 from Account where (not field1 like '%GEN') and field2 != null

| improve this answer | |
  • It's actually nothing to do with LIKE. NOT requires parentheses when it would otherwise be ambiguous. where not field1 like '%gen' works, but where not field1 like '%gen' and field2 != null is ambiguous, so you have to use the parentheses in order to get the query to work. – sfdcfox Dec 10 '16 at 19:11

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.