0

I am trying to re-word this code in a custom controller:

 public Double getFebruary() {
    Double amountX = (Double)[SELECT SUM(Amount)amt FROM Opportunity WHERE RecordTypeId =:oppNBRecordTypeId AND CALENDAR_MONTH(Billing_Effective_Date__c) = 2 AND StageName =:stage][0].get('amt');
    return amountX;
} 

to:

// Database queries:
    public String soqlQuery1 = '[SELECT SUM(Amount)amt FROM Opportunity WHERE RecordTypeId =:oppNBRecordTypeId AND StageName =:stage AND Billing_Effective_Date__c = THIS_YEAR AND CALENDAR_MONTH(Billing_Effective_Date__c) = ';
    public String soqlQuery2 = '][0].get(\'amt\')';

    public List<Double> getFebruary() {
    Double[]amountX = (Double[])database.Query(soqlQuery1 + '1' + soqlQuery2);
    return amountX;
    }

Reason being is that as the class will have many methods in a similar fashion (getMarch, getApril etc) where the only difference is the CALENDAR_MONTH(Billing_Effective_Date__c) argument, which will increment by 1 with each method, I figure that using the Database.Query() method will help to reduce the length of the class.

I am returning the error 'Incompatible types since an instance of List is never an instance of List'. I've tried a few different attempts but to no avail. I know Database.Query() returns a list of SObjects. Am I not able to cast the resut of Database.Query() method from a list of Sobjects to a List or variable of type Double?

3

No, you just need to make build your String soql based on what's inside the square brackets ([]). None of the code outside the brackets (or including them) is part of the query. Here's the code you're trying to build dynamically:

Double amountX = (Double)Database.query('SELECT SUM(Amount) amt FROM Opportunity WHERE RecordTypeId =:oppNBRecordTypeId AND CALENDAR_MONTH(Billing_Effective_Date__c) = 2 AND StageName =:stage')[0].get('amt');

More clear to see:

SObject record;

record = [/*soql*/][0];
// OR
record = Database.query('/*soql*/')[0];

Double amount = (Double)record.get('amt');
  • Thanks @Adrian. This is my code now, with your suggestion. The class compiles on the page, it's returning the '* is not valid' error msg. I think i'm getting confused with the syntax to concatenate the dynamic SOQL from the variable 'soql' with the integer (2). public class SummaryTest { public Double getJanuary() { sObject record = Database.query('/*soql*/'+'/2/')[0]; Double amountX = (Double)record.get('amt'); return amountX; } } – Andy Hitchings Jul 18 '16 at 17:01
  • Where: String soql = ' String soql = 'SELECT SUM(Amount)amt FROM Opportunity WHERE RecordTypeId =:oppNBRecordTypeId AND StageName =:stage AND Billing_Effective_Date__c = THIS_YEAR AND CALENDAR_MONTH(Billing_Effective_Date__c) ='; and where the bind variable oppNBRecordTypeId is specified earlier in the class as: public Id oppNBRecordTypeId = [SELECT RecordTypeId FROM Opportunity WHERE RecordType.Name = 'New Business - Read Only' LIMIT 1].Id; – Andy Hitchings Jul 18 '16 at 17:03
  • I used /*soql*/ and '/*soql*/' in place of typing out the whole query since it is quite long and makes it difficult to illustrate the point. @AndyHitchings The first line is what you would actually copy-paste into your code. – Adrian Larson Jul 18 '16 at 17:04
  • I don't really see any benefit then using this instead of the first method I have in my question. I thought I could create a string variable and just parse that into Database.query() and then concatenate it with '2' or '3' or '4' etc depending on the month it should append to the final piece of the SOQL (CALENDAR_MONTH(Billing_Effective_Date__c = 1). Thanks anyway for your help :) – Andy Hitchings Jul 18 '16 at 17:15
  • @AndyHitchings Well that's not the question you asked. You need to think more carefully about how you frame your questions if you want to get an answer that addresses the root issue instead of a superficial issue you have run into in getting there. – Adrian Larson Jul 18 '16 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.