[SalesForce] Assigning an integer variable a value from dynamic SOQL query

I was hoping I can get some help.

So I have a wrapper where populating an integer variable inside a for-loop, with a SQL COUNT() query works well. example:

eachwrap.numOpsPM = [SELECT Count() from operational_projects__c where Project_Manager_Lookup__c =:StringID ];

Now, I want to add filters dynamically, so I change it to what's below. It doesn't work, since the result is a list.

soqlpm = 'SELECT Count() from operational_projects__c where Project_Manager_Lookup__c =:StringID '    
eachwrap.numOpsPM = database.query(SoqlPM)

I've tried making a temporary list variable to hold the list value, and then convert it back to integer.

varOpstemp= database.query(soqlPM) ;
if (varOpsTemp.size() >0 ){
           eachwrap.numOpsPM = integer.valueof(varOpsTemp[0]);
}

This doesn't work, but instead gives me a "use countQuery() for [select count()…] queries" Error, in my exception capture.

Obviously I'm going about this the wrong way. I couldn't find anything on countQuery() at ALL. Can someone point me in the right direction?

Best Answer

You're looking for the Database.countQuery method.

eachwrap.numOpsPM = database.countQuery(SoqlPM);

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

This is untested so you may need to have play, however this should return a List of the RevenueWrapper classes which contains the 4 columns you are looking to use in your VF page:

public class RevenueController{

Map<String, RevenueWrapper> ResultList = new Map<String, RevenueWrapper>(); 

public RevenueController(){
    List<AggregateResult> result =  [
    SELECT FISCAL_YEAR(Date__c) Date, Type__c, sum(Amount__c) Amount
    FROM Revenue__c
    GROUP BY FISCAL_YEAR(Date__c), Type__c];

    for(AggregateResult ar : result){

        RevenueWrapper RW = new RevenueWrapper();

        //If the RevenueWrapper already exists grab it from the Map
        if(ResultList.containsKey(String.valueOf(ar.get('Date')))){
            RW = ResultList.get(String.valueOf(ar.get('Date')));
        }

        //Set the amount
        RW.setAmount(ar);

        //Save back to the Map
        ResultList.put(String.valueOf(ar.get('Date')), RW);
    }
}

public List<RevenueWrapper> getResults() {
    return ResultList.values();
}


public class RevenueWrapper{
    public String revenueDate { get; set; }
    public Integer budgetAmount { get; set; }
    public Integer actualAmount { get; set; }
    public Integer varianceFYPercent { set; }
    public Integer varianceFYAmount { set; }

    public RevenueWrapper(){

    }

    public void setAmount(AggregateResult ar){
        revenueDate = String.valueOf(ar.get('Date'));

            //Set the amount value based on type
        if(ar.get('Type__c') == 'Budget'){
            budgetAmount = Integer.valueOf(ar.get('Amount'));
        }else if(ar.get('Type__c') == 'Actual'){
            actualAmount = Integer.valueOf(ar.get('Amount'));
        }else{ //Catch all for a type that isn't recognised
            return;
        }

    }

    public Integer getVarianceFYAmount(){
        return actualAmount - budgetAmount;
    }

    public Decimal getVarianceFYPercent(){
        if(budgetAmount != 0){
            return Decimal.valueOf((Double.valueOf(actualAmount) / Double.valueOf(budgetAmount)) * 100).setscale(1);
        } else{
            return 0;
        }
    }
}

}

[SalesForce] SOQL inline query vs dynamic query

SOQL Inline query has advantages if you can work within its less dynamic structure. Mainly, when your Apex is compiled it checks references to fields and objects in the inline SOQL. Compiler errors are good! It makes it less likely that you will run into strange runtime behavior

Database.query() is powerful, but since it queries on a string constructed by you, it is much more error prone.

In my experience, there have been two typical use cases where I have used Dynamic SOQL:

In a Visualforce page where I want to dynamically change the query, order by, columns queried, etc. based on user actions.
When creating dynamic Apex functionality where I want to query for any sObject type, but I won't know until I am passed that sObject type. The Apex describe features can help a lot with this.

Related Topic