Skip to main content
Topics#Salesforce DeveloperMiller Randy 的问题
Miller Randy (Self) 提问于 #Salesforce Developer
2022年5月27日 20:36

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?

 
3 个回答
  1. Maharajan Chinnadurai (Infosys Limited)
    2022年5月27日 22:16
    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
撰写回答...
0/9000