0

Trying to update an OpportunityLineItem, but need to be able to look for that particular product in the opportunity.

Problem:

SOQL is using the List Price from the product but from a different opportunity

Error:

in (SELECT OpportunityId FROM OpportunityLineItem)LIMIT 1 ^ ERROR at Row:4:Column:103 The inner and outer selects should not be on the same object type

I understand what the error is, but can't figure out a solution to get what I need. Any ideas?

SOQL

OpportunityLineItem optLIProductB = [SELECT OpportunityId, Opportunity.Pricebook2Id, Name, ProductCode , PricebookEntryId, Quantity, UnitPrice
                                                        FROM OpportunityLineItem
                                                        WHERE ProductCode = '1b0000' 
                                                        AND OpportunityId in (SELECT OpportunityId FROM OpportunityLineItem)LIMIT 1];
  • I don't understand why you are using that as your filter criteria. As written (assuming it compiles, which it wont) that query would pull all records, but only return the first record. Are you trying to query for all line items associated with a specific opportunity? – battery.cord Oct 13 '17 at 14:58
  • That's the problem. It brings up all the records, but I'm only interested in the product (in this cast with productcode 1b0000) that is in the current opportunity. Does that make sense? – user48169 Oct 13 '17 at 15:19
  • Then you need to use that record id in your query filter. The how depends on your context, which isn't specified in your question. – battery.cord Oct 13 '17 at 15:23
  • Do you have an ID of an Opportunity you are working with? – Andrii Muzychuk Oct 13 '17 at 15:43
  • The account ID is different every time, so I can't put that in the code. This query is part of a trigger where I'm trying to update the Sale Price of a product based on the value of another product. – user48169 Oct 13 '17 at 15:52
0

You need to query by a particular id, or a list of ids, instead of all available ids. You can use a bind to provide an apex variable to a query for use in a filter.

From the Using Apex Variables in SOQL:

Account A = new Account(Name='xxx');
insert A;
Account B;

// A simple bind
B = [SELECT Id FROM Account WHERE Id = :A.Id];

You can also use a Id or a List<Id>.

List<Id> ids = new List<Id>(); // set from somewhere, or get from a list of sObjects
Id someId = 'a0Cc000000PAWBv'; // For example only, avoid hardcoding ids

List<Account> accounts = [SELECT ID, Name FROM Account WHERE Status = 'Active' AND Id IN :Ids];

Account account = [SELECT ID, Name FROM Account WHERE Status = 'Active' AND Id = :someId LIMIT 1];

This is a general example on how to use these variables in queries. Try to modify your code to use these, and either edit your question, or ask a new question if you encounter issues with your code.

-1
Id opportunityId;
// assign opportunityId with proper Opportunity you are working with
List<OpportunityLineItem> optLIProductB = [SELECT OpportunityId, Opportunity.Pricebook2Id, Name, ProductCode, PricebookEntryId, Quantity, UnitPrice
                                                        FROM OpportunityLineItem
                                                        WHERE ProductCode = '1b0000' 
                                                        AND OpportunityId :opportunityId
];

Please, don't query to a single object. Query in List. Then, you can check the size of the list. In case of single object you may have System.QueryException.

Your Answer

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