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?