I've tried re-writing this query a few times, and no matter how I separate out the And/Or parts, it doesn't seem to want to work. I consistently return zero results.

This query isn't working (and I need help fixing it):

[select Id, Account_Domain_Hidden__c, Opps__c, Type 
 from Account 
 where (Account_Domain_Hidden__c IN :acctQueryLimiter2) AND 
       ((Type = 'Customer') or                                                                                                                                                         
        ((Type = 'Prospect') AND (Opps__c > 0))

However this query is working (for reference, so you know the issue involves discrepancies between the two):

[select Id, Account_Domain_Hidden__c 
  from Account where Account_Domain_Hidden__c IN :acctQueryLimiter2]

Note: I did use Query Editor and determine that the Opps__c field if queried independently is returning values, as is the type Field. Presently there aren't any accounts with label of Type 'Prospect', but that is an established label/value option for the field. So it shouldn't be effecting the query in such a way as to cause zero results to be returned, which is what's happening presently, so I'm certain the issue comes from my structuring and separation of the and/or values, I'm just not sure how to re-structure it.

Desired result is to retrieve the requested values from account when the following conditions are met:

1) The account's Account_Domain_Hidden__c value matches a value found in theacctQueryLimiter2 list

2) The Account Type is either 'Customer' or 'Prospect'

2A) And if Account Type is 'Prospect', Opps_c value must be greater than 0.

How can I re-write this query to obtain the desired result?


SELECT Id, Account_Domain_Hidden__c, Opps__c, Type
FROM Account
WHERE Account_Domain_Hidden__c IN :acctQueryLimiter2
    Type = 'Customer' OR
    (Type = 'Prospect' AND Opps__c > 0)

Seems like it should work. Have you verified that your Prospect accounts have Opps? Alternatively, have you verified that you have Customer Accounts in your desired result set?

| improve this answer | |

I think the problem is in part that you've located your first parens just after the WHERE clause begins and have an extra parens at the end (perhaps that was part of a map query?). Try something like the following:

[select Id, Account_Domain_Hidden__c, Opps__c, Type from Account where Account_Domain_Hidden__c IN :acctQueryLimiter2 AND 
((Type = 'Customer') or ((Type = 'Prospect') AND (Opps__c > 0)))];
| improve this answer | |
  • Tried it, nothing comes back in the query still. – LMBB Feb 5 '15 at 17:45
  • Do you get results if you leave off everything from just before the first AND? Is Opps__c a decimal, integer or a text string? – crmprogdev Feb 5 '15 at 18:14
  • Sorted it out! 1) The account I was trying to locate had its Record Type field set to Customer, but not its Type field set to Customer - which was why it couldn't find it. Rookie mistake, oops! Thanks Adrian Larson for thinking to ask if the issue was with the data being queried, not the query itself. 2) My SOQL was wrong - So crmprogdev, the query you suggested worked as soon as I fixed issue #1. Thanks! – LMBB Feb 11 '15 at 14:31

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.