Skip to main content
Topics#Salesforce DeveloperPergunta de Miller Randy
Miller Randy (Self) perguntou em #Salesforce Developer
27 de mai. de 2022, 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 respostas
  1. Maharajan Chinnadurai (Infosys Limited)
    27 de mai. de 2022, 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
Escrever uma resposta...
0/9000