[SalesForce] SOQL Query isn’t working… Where with multiple In And Or

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?

Thanks!

Best Answer

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

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?