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?



2 Answers 2

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?


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)))];
  • Tried it, nothing comes back in the query still.
    – LMBB
    Feb 5, 2015 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, 2015 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, 2015 at 14:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .