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
Eduardo Rodriguez 23Eduardo Rodriguez 23 

Winter 18 - Problem with Dynamic SOQL referencing Formula Field (ORA-00936)

We have a custom object with a Formula field referncing the Amount field in the Opportunity object. In apex, we use dynamic SOQL to generate a query that includes this field. When the code executes in Winter 18 (Partial Sandbox), we are receiving this error when the query executes: System.UnexpectedException: common.exception.SfdcSqlException: ORA-00936: missing expression. 

Exact same code is working fine in Production (Summer 17). We tested in a Developer Sandbox running Winter 18 and it is working fine too. Compared the metadata between the two sandboxes and seems to be the same. Anyone else seeing problems with dynamic SOQL queries in Winter 18? 
LBKLBK
Hi Eduardo,

Can you please post your APEX code here, so that some of us can take a look?
Eduardo Rodriguez 23Eduardo Rodriguez 23
When Opportunity is created or updated -> 
  
trigger onOpportunity on Opportunity (before insert, before update, after update, after insert) {
        
  ... 
    
    if(Trigger.isBefore && Trigger.isUpdate)
    {
        OpportunitiesFlag.opportunityUpdateTransaction = true;
        Opportunities opportunitiesInBeforeUpdate = new Opportunities(Trigger.old, Trigger.new); 
        opportunitiesInBeforeUpdate.performBeforeUpdateValidations(); // ERROR HERE
        opportunitiesInBeforeUpdate.performBeforeUpdateFieldChanges();
    }

    ...

}

Opportunities class:
  
...

public void performBeforeUpdateValidations()
    {
        System.debug('@@ANM - Entering Opportunities - performBeforeUpdateValidations');

        if(newRecordsMap == null || newRecordsMap.isEmpty()) return;
        if(oldRecordsMap == null || oldRecordsMap.isEmpty()) return; 

        Boolean blockChangesIfAlreadyInSAForQuoting = CRMSettingsService.blockChangesAfterSAForQuoting();
        Boolean hasAccessToChangeOppAfterSAForQuoting = ANMUtils.hasCustomPermission('CRM_CanChangeValuesAfterSAForQuoting');
        String cannotChangeMessage = ' cannot be updated from Salesforce once the opportunity has been sent to SA. Please update from Connectwise.';

        System.debug('@@ANM - Performing Before Update Validation with blockChangesIfAlreadyInSAForQuoting:'+blockChangesIfAlreadyInSAForQuoting );
        System.debug('@@ANM - Performing Before Update Validation with hasAccessToChangeOppAfterSAForQuoting:'+hasAccessToChangeOppAfterSAForQuoting );

        List<Id> oppIdList = new List<Id>();
        oppIdList.addAll(newRecordsMap.keySet());

        OpportunityReviews currentOpportunityReviews = new OpportunityReviews('OPP-ID', oppIdList);  // ERROR HERE

 ...
    }

....

OpportunityReviews class:
  
public OpportunityReviews(String strategy, List<Id> values)
    {
        if(strategy == 'OPP-ID')
        {
            ANMQueryStringFactory query = new ANMQueryStringFactory('Opportunity_Review__c');
            Set<String> whereClauses = new Set<String>();
            whereClauses.add('Opportunity__c IN: values');
            query.addWhereClauses(whereClauses);
            System.debug('@@ANM - Performing OpportunityReviews Query with: '+query.text);
            records = Database.query(query.text); // EXCEPTION THROWN HERE -> System.UnexpectedException: common.exception.SfdcSqlException: ORA-00936: missing expression
        }
       ... 
    }

At execution time query.text =  
@@ANM - Performing OpportunityReviews Query with: SELECT id, ownerid, isdeleted, name, currencyisocode, createddate, createdbyid, lastmodifieddate, lastmodifiedbyid, systemmodstamp, lastactivitydate, lastvieweddate, lastreferenceddate, account_manager__c, allow_creation_or_update__c, approver_comments__c, ex_review__c, ms_amount__c, ms_review__c, opportunity_amount__c, opportunity_name__c, opportunity__c, overwritten__c, ps_amount__c, ps_review_level__c, ps_review__c, primary_se__c, review_practice__c, status__c, type_of_review__c, on_hold__c, autoapprove__c FROM Opportunity_Review__c WHERE Opportunity__c IN: values
We have done some troubleshooting and if we remove the opportunity_amount__c field from the query, the code executes successfully. opportunity_amount__c is a formula field (currency) with the following formula: Opportunity__r.Amount in the Opportunity_Review__c object. The same code is executing without this error in Production (Summer 17). 
​