+ Start a Discussion
Randy Miller 35Randy Miller 35 

Resolve SOQL inner select field 'Opportunity.Id' cannot have more than one level of relationships

Hello, trying to compose a SOQL that will retrieve recently Closed Won Opportunities unless the Opportunity has only 1 of 2 specific Products. Here is my last attempt:

SELECT Id, CloseDate, Name, Owner.Name, Account.Name FROM Opportunity WHERE StageName = 'Closed Won' AND CloseDate = LAST_N_DAYS:30 AND Id NOT IN (SELECT Opportunity.Id FROM OpportunityLineItem WHERE Opportunity.StageName = 'Closed Won' AND Opportunity.CloseDate = LAST_N_DAYS:30 AND Opportunity.CountofProducts__c = 1 AND PricebookEntry.Product2.Name NOT IN ('Product Name 1', 'Product Name 2') )

FYI, I've tried to qualify the sub-select as much as possible to avoid limit errors, and Opportunity.CountofProducts__c is a basic custom Rollup Summary COUNT of Opportunity Products.

I am encountering this error:
MALFORMED_QUERY:
LAST_N_DAYS:30 AND Id NOT IN (SELECT Opportunity.Id FROM OpportunityLineItem
^ (points to the space between SELECT and Opportunity)
ERROR at Row:1:Column:155
The inner select field 'Opportunity.Id' cannot have more than one level of relationships

Any suggestions for how to re-write?
 
Best Answer chosen by Randy Miller 35
Maharajan CMaharajan C
Hi Randy,

Please try the below SOQL:

Opportunity.Id  ==== >   OpportunityId  remove the dot
 
SELECT Id, CloseDate, Name, Owner.Name, Account.Name FROM Opportunity WHERE StageName = 'Closed Won' AND CloseDate = LAST_N_DAYS:30 AND Id NOT IN (SELECT OpportunityId FROM OpportunityLineItem WHERE Opportunity.StageName = 'Closed Won' AND Opportunity.CloseDate = LAST_N_DAYS:30 AND Opportunity.CountofProducts__c = 1 AND PricebookEntry.Product2.Name NOT IN ('Product Name 1', 'Product Name 2') )

Thanks,
Maharajan.C

All Answers

Maharajan CMaharajan C
Hi Randy,

Please try the below SOQL:

Opportunity.Id  ==== >   OpportunityId  remove the dot
 
SELECT Id, CloseDate, Name, Owner.Name, Account.Name FROM Opportunity WHERE StageName = 'Closed Won' AND CloseDate = LAST_N_DAYS:30 AND Id NOT IN (SELECT OpportunityId FROM OpportunityLineItem WHERE Opportunity.StageName = 'Closed Won' AND Opportunity.CloseDate = LAST_N_DAYS:30 AND Opportunity.CountofProducts__c = 1 AND PricebookEntry.Product2.Name NOT IN ('Product Name 1', 'Product Name 2') )

Thanks,
Maharajan.C
This was selected as the best answer
AnkaiahAnkaiah (Salesforce Developers) 
Hi Randy,

As Maharajan said, you need to add opportunityid instead of opportunity.id.

And already you were filtering the opporunities with closed won and no need to filter them in opportunitylineitem.
 
SELECT Id, CloseDate, Name, Owner.Name, Account.Name FROM Opportunity WHERE StageName = 'Closed Won' AND CountofProducts__c = 1 AND CloseDate = LAST_N_DAYS:30 AND Id 
NOT IN (SELECT OpportunityId FROM OpportunityLineItem WHERE PricebookEntry.Product2.Name NOT IN ('Product Name 1', 'Product Name 2') )

If this helps, please mark it as best answer.

Thanks!!​​​​​​​
Randy Miller 35Randy Miller 35
Thanks, Maharajan.C and Ankaiah! 
I was running into a limit error with the subselect so, by replicating some of the filters there, I was able to get by the limit error.
It's working okay now!