0

UPDATE: Changed query from dynamic to static. Still have malformed problem.

I have a malformed query problem. Could somebody help? It works in my batch class, but not my normal class.

            [SELECT Id, Name, AccountId, Account.Name, StageName, CloseDate, CreatedBy.Name, CreatedDate, LastModifiedBy.Name, LastModifiedDate,
                 (SELECT Id, Collection_Frequency__c, PricebookEntry.Product2.Name, PricebookEntry.Product2.Id, PriceBookEntry.Id, UnitPrice, Quantity, Total_Lifts_Per_Year__c, PricebookEntry.Product2.Family, ServiceDate, Split__c
                  FROM OpportunityLineItems
                  WHERE Active_Product__c = true 
                  AND PricebookEntry.Product2.Product_Type__c != 'Bag Service' 
                  AND Collection_Frequency__c != 'Call Off' 
                  AND PricebookEntry.Product2.Name != 'CheaperConsumables Membership'
                  AND ((Collection_Frequency__c = 'Collection')
                       OR (Split__C = false AND CALENDAR_MONTH(ServiceDate) =: todaysMonth AND (PriceBookEntry.Product2.Name = 'DOC' OR PriceBookEntry.Product2.Name = 'Bin Insurance'))
                       OR (Split__C = true AND DAY_IN_MONTH(ServiceDate) =: todaysDay AND (PriceBookEntry.Product2.Name = 'DOC' OR PriceBookEntry.Product2.Name = 'Bin Insurance'))))
             FROM Opportunity
             WHERE CloseDate <=: closeDateLimit AND Requires_Sales_Invoice__c = true AND Sales_Invoice_Created__c = false AND (StageName = 'WASTE SALE!' OR StageName = 'Bank Details Required')
             order by StageName DESC, Requires_Sales_Invoice_Date__c ASC]));

Error:

MALFORMED_QUERY: AND CALENDAR_MONTH(ServiceDate) = :tmpVar1 AND (PriceBookEntry.Product2.Name ^
ERROR at Row:1:Column:674 rule parseSingleWhere failed predicate: {c != null}?

The code runs successfully if I replace todaysMonth and todaysDay with hardcoded values.

todaysMonth and todaysDay are Integer type variables that are equal to today.month() and today.day()

The query runs in the query editor. I have the query debugging and returning records.

I really have no idea why its breaking.

3
  • Better to use static query in order to avoid syntax errors.
    – Reshma
    Jan 21, 2019 at 11:34
  • I've changed it to static, and it's still malformed. It complains about "tmpVar1" which is an integer variable. Also if I use a hardcoded value such as 01, the malformed error goes away, but gives a different error. Jan 21, 2019 at 11:51
  • You need to revisit your logic and add condition !System.isFuture() || !System.isBatch() to prevent the exception.
    – Reshma
    Jan 21, 2019 at 12:08

1 Answer 1

2

It would appear that bind variables can't be used when the field expression is a Date function

Integer date_mm;
Opportunity[] opps = [select id from Opportunity where Calendar_Month(closeDate) = :date_mm];

MALFORMED_QUERY: unexpected token: Integer

You'll need to use dynamic soql instead

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .