The "WHERE IN" clause SOQL

Question

I am trying to get a list of OpportunitiyHistory for selection of Opportunities I do:

   // Get all the opportunities modified in a date range.   
   List<Opportunity> opportunities = [SELECT Opportunity.OwnerId, Owner.Name 
            FROM Opportunity    
            where Opportunity.LastModifiedDate = LAST_N_DAYS:7];

   // Now get the corresponding history for these opportunities 
   List<OpportunityHistory> oppHistories = [SELECT Id, Probability, OpportunityID 
              From OpportunityHistory 
              where OpportunityID in:=opportunities];

I get:

Multiple markers at this line
    - Save error: unexpected 
     token: '='
    - unexpected token: '='

Any ideas what I am doing wrong?

Answer 1

: is sufficient indication that you want to bind another variable.

List<OpportunityHistory> oppHistories = [SELECT Id, Probability, OpportunityID 
    From OpportunityHistory 
    where OpportunityID IN :opportunities];

Or even better - with one query:

SELECT Opportunity.OwnerId, Owner.Name,
    (SELECT Id, OldValue, NewValue FROM Histories WHERE Field = 'Probability'),
    (SELECT Id, Probability, StageName FROM OpportunityHistories)
FROM Opportunity
WHERE LastModifiedDate = LAST_N_DAYS:7

This goes to 2 related lists, regular "field history tracking" as well as special thing available only for opportunities that tracks Probability & Stage only. Very useful if you need to have them automapped to the Opportunity.

Yet another format that wastes only 1 query:

SELECT Opportunity.OwnerId, Opportunity.Owner.Name, Probability, StageName
FROM OpportunityHistory 
WHERE Opportunity.LastModifiedDate = LAST_N_DAYS:7
ORDER BY OpportunityId, CreatedDate

Answer 2

Remove the "=" from the last line and you're good. You have in:= list, you need in: list

Answer 3

The placeholder syntax doesnt need the = it should just be :opportunities

   // Now get the corresponding history for these opportunities 
   List<OpportunityHistory> oppHistories = [SELECT Id, Probability, OpportunityID 
              From OpportunityHistory 
              where OpportunityID in:opportunities];

Although if you dont need other information you can combine your queries to:

List<OpportunityHistory> oppHistories = [SELECT Id, OpportunityId 
    FROM OpportunityHistory 
    WHERE OpportunityId IN (
        SELECT Id 
            FROM Opportunity    
            WHERE Opportunity.LastModifiedDate = LAST_N_DAYS:7
    )
];