SOQL Query isn't working... Where with multiple In And Or

Question

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!

Answer 1

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?

Answer 2

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