Apex Code Development You need to sign in to do that
Don't have an account?
Randy 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?
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?
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
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
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!!
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!