Database.Query() method casting issue

Question

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?

Answer 1

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');