function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mike_MMike_M 

Why does this not work. (Simple code question)

Hi, I am working through the totorial and came to the part for triggers. Below is the code straight from the tutorial PLUS one extra line that I added shown in red. 

 

trigger HandleProductPriceChange on Merchandise__c (after update) {
    List<Line_Item__c> openLineItems =
        [SELECT j.Unite_Price__c, j.Merchandise__r.Price__c
         FROM Line_Item__c j
         WHERE j.Invoice_Statement__r.Status__c = 'Negotiating'
         AND j.Unite_Price__c < j.Merchandise__r.Price__c
         AND j.Merchandise__r.id IN :Trigger.new
         FOR UPDATE];
    for (Line_Item__c li: openLineItems) {
        if ( li.Merchandise__r.Price__c < li.Unite_Price__c ){
        li.Unite_Price__c = li.Merchandise__r.Price__c;
        }
    }
    update openLineItems;
}
When I try to SAVE I get a compile error on the line I added.  [unexpected token: j.Merchandize__r.Price__c]
Why does my new line of code not work?
(Note: Unite_Price really IS the field name. It's a typo that I never went back to fix.)
Thanks,
Mike

 

Best Answer chosen by Admin (Salesforce Developers) 
Starz26Starz26

To my knowledge, SOQL cannot compare value of two fields within the SOQL statement.

 

To Accomplish this you could:

 

Map<ID,Line_Item__c> openLineItems =
        New Map<ID,Line_Item__c>([SELECT j.Unite_Price__c, j.Merchandise__r.Price__c
         FROM Line_Item__c j
         WHERE j.Invoice_Statement__r.Status__c = 'Negotiating'
         AND j.Merchandise__r.id IN :Trigger.new
         FOR UPDATE]);
For(Line_Item__c li : openLineItems.Values()){
     if(li.Unit_Price__c != li.merchandise__r.Price__c)
           OpenLintItems.remove(li.id);
}

 

All Answers

Andy BoettcherAndy Boettcher

Typo?  In your post:

 

 [unexpected token: j.Merchandize__r.Price__c]

 

The field appears to be  j.Merchandise__r.Price__c

 

-Andy
Mike_MMike_M

Yes, I mis-typed the error message. Sorry about that.

 
Mike_MMike_M

Also, please note that both of the column names in the line I added (in red) are also named in the SELECT list. How can these two columns be understood within the context of the SELECT list, but not within the context of the WHERE clause?

 
Mike_MMike_M

More info:

This compiles ...

 

    List<Line_Item__c> openLineItems =
        [SELECT j.Unite_Price__c, j.Merchandise__r.Price__c
         FROM Line_Item__c j
         WHERE j.Invoice_Statement__r.Status__c = 'Negotiating'
          AND  j.Unite_Price__c = 1
          AND  j.Merchandise__r.Price__c = 1
         AND j.Merchandise__r.id IN :Trigger.new
         FOR UPDATE];
but this does not 
    List<Line_Item__c> openLineItems =
        [SELECT j.Unite_Price__c, j.Merchandise__r.Price__c
         FROM Line_Item__c j
         WHERE j.Invoice_Statement__r.Status__c = 'Negotiating'
         AND  j.Unite_Price__c = j.Merchandise__r.Price__c
         AND j.Merchandise__r.id IN :Trigger.new
         FOR UPDATE];
Why does it not like j.Merchandise__r.Price__c as a right-and operand, but it is fine
if it's on the left-hand side?
Maybe I am expecting to this to behave too much like SQL.
Starz26Starz26

To my knowledge, SOQL cannot compare value of two fields within the SOQL statement.

 

To Accomplish this you could:

 

Map<ID,Line_Item__c> openLineItems =
        New Map<ID,Line_Item__c>([SELECT j.Unite_Price__c, j.Merchandise__r.Price__c
         FROM Line_Item__c j
         WHERE j.Invoice_Statement__r.Status__c = 'Negotiating'
         AND j.Merchandise__r.id IN :Trigger.new
         FOR UPDATE]);
For(Line_Item__c li : openLineItems.Values()){
     if(li.Unit_Price__c != li.merchandise__r.Price__c)
           OpenLintItems.remove(li.id);
}

 

This was selected as the best answer
Mike_MMike_M

Thanks. 
Starz26Starz26

See my update post on how to accomplish this with the one soql statement using a map
Mike_MMike_M

Cool, thanks again!