'Expecting colon' error in SOQL query when not using a variable [duplicate]

Question

In the Anonymous Window of the developer console, I am attempting to execute the following:

System.debug(
    JSON.serialize([
        SELECT OpportunityLineItemSchedule.Revenue, OpportunityLineItemSchedule.ScheduleDate, OpportunityLineItemSchedule.OpportunityLineItem.TotalPrice, OpportunityLineItemSchedule.OpportunityLineItem.Opportunity.Id, OpportunityLineItemSchedule.OpportunityLineItem.Opportunity.CloseDate, OpportunityLineItemSchedule.OpportunityLineItem.Opportunity.Amount
        FROM OpportunityLineItemSchedule
        WHERE OpportunityLineItemSchedule.OpportunityLineItem.Opportunity.Amount != OpportunityLineItemSchedule.OpportunityLineItem.TotalPrice
    ])
);

I receive this error:

Line: 5, Column: 78
expecting a colon, found 'OpportunityLineItemSchedule.OpportunityLineItem.TotalPrice'

I understand this error typically pops up when one is attempting to use a variable in a query. However, I am not: I am using standard fields. Why am I receiving this error?

Answer 1

You can't have a field reference on the right side of a filter; SOQL, while similar to SQL, is definitely not SQL.

The following example is also illegal:

SELECT Id 
FROM Opportunity
WHERE Account.Name = Name

If you need to compare two fields together, you need to make a custom formula field, or query the records (potentially all of them), and compare them to each other. Assuming I remember correctly, the Schedule objects don't support custom fields, including formulas.

Note that any way you go about it, you're looking at a full table scan, so expect problems if you have a large number of records. You might want to narrow down your search via other techniques (such as filtering on an account name or something).