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