1

I'm doing this query

Select id, name, (select quantity, PriceBookEntry.Name from OpportunityLineItems where quantity  != NULL)  from Opportunity where StageName = 'Closed Won'

And it'll return a bunch of Opportunities, but for some of them, OpportunityLineItems will be null.

How can I only return the Opportunities that have OpportunityLineItems?

Doing and OpportunityLineItems != NULL makes the query crash with

Invalid Field [object Object]: where StageName = 'Closed Won' and OpportunityLineItems != NULL ^ ERROR at Row:1:Column:158 No such column 'OpportunityLineItems' on entity 'Opportunity'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.
2

How about filtering if the Opportunity ID is in the OLI table

    Select id, name, (select quantity, PriceBookEntry.Name from OpportunityLineItems where quantity != NULL) from Opportunity where StageName = 'Closed Won' AND ID in (select OpportunityID from OpportunityLineItem)
| improve this answer | |
1

You can query the OpportunityLineItem object limiting the set returned by checking the parent ID like this:

select Quantity, PriceBookEntry.Name, Opportunity.Id, Opportunity.Name
from OpportunityLineItem
where Quantity != null
and OpportunityId in (select Id from Opportunity where StageName = 'Closed Won')

That automatically eliminates Opportunity objects that have no OpportunityLineItems.

| improve this answer | |
  • @Houseman My fault, forgot to remove the "s". Try the version in the answer now. – Keith C Jun 17 '15 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy