1

With this code, when I execute the query in the inspector I get the result, however when I call my class in my component I do not retrieve Data and I get this error in the console:

      message : "expecting a right parentheses"

and tells me that the error that exists in the last line of code:

     cumul = database.query(Query);

some help please .

 @AuraEnabled(cacheable=true)
    public static List <AggregateResult> getMonthly(){
    User u = [SELECT id, Volume__c FROM  User WHERE id=: UserInfo.getUserId()];
    List<AggregateResult> cumul = new list<AggregateResult>();
    List<Account> acc = [SELECT Id,RecordType.DeveloperName FROM Account WHERE ownerid =: UserInfo.getUserId()];
    List<String> accString = new List<String>();
        for( Account c: acc)
            {
            accString.add(c.Id);
            }
    String year = String.valueOf(System.Today().year());
    String  productFamily = 'light';
    String Filters ='';
    Filters = Filters + 'Year__c = \'' + year +'\' ';
    Filters = Filters + 'AND Sales__r.Account__r.Station_Account__c IN '+ accString +' ';    
    Filters = Filters + 'AND Product_Family__c = \''+ productFamily +'\' ';
    String Query = 'SELECT Sales__r.Account__r.Name ,CALENDAR_MONTH(Date_technicalfield__c) Month, ';    
        if (u.Volume__c == 'Liter') {
            Query = Query + 'SUM(Quantity_in_L__c) Quantity ';
        } else {
            Query = Query + 'COUNT(id) Quantity ';
        }
        Query = Query + 'FROM SalesK__c WHERE ' + Filters + 'GROUP BY Sales__r.Account__r.Name ,Date_technicalfield__c ORDER BY Date_technicalfield__c ASC';
    cumul = database.query(Query);
    return cumul;
    }

2
  • Could you please share the js of your component? Nov 6, 2022 at 19:38
  • 1
    Well, if this is an error in the Apex portion, then the first step would be to system.debug(Query); to see if there is anything wrong with the way you're building your query.
    – Derek F
    Nov 6, 2022 at 19:43

2 Answers 2

1

I think it's likely your IN syntax that is causing problems.

If you want to build your own, you need to have make something like:

IN('value','value','value')

and you need to build this syntax up yourself (including escaping quote marks)

However, if you use binding, the system will use local context variables and do it for you.

Change your IN syntax to:

Filters = Filters + 'AND Sales__r.Account__r.Station_Account__c IN :accString ';  

Further example (this is the whole method):

User u = [SELECT Id, Volume__c FROM  User WHERE id=: UserInfo.getUserId()];
Account[] accounts = [SELECT Id,RecordType.DeveloperName FROM Account WHERE ownerid =: UserInfo.getUserId()];

String year = String.valueOf(System.Today().year());
String  productFamily = 'light';
String filters = '' 
 + ' Year__c = :year '
 + ' AND Sales__r.Account__r.Station_Account__c IN :accounts '  
 + ' AND Product_Family__c = :productFamily ';  
 
String query = ' SELECT Sales__r.Account__r.Name, CALENDAR_MONTH(Date_technicalfield__c) Month, '
 + u.Volume__c == 'Liter' ? ' SUM(Quantity_in_L__c) Quantity ' : ' COUNT(id) Quantity ' 
 + ' FROM SalesK__c WHERE ' + filters + ' GROUP BY Sales__r.Account__r.Name, Date_technicalfield__c ORDER BY Date_technicalfield__c ASC';
              
return (List<AggregateResult>)Database.query(query);

As you can see, this is about HALF the amount of code in your example.

There are some useful lessons here that you managed to avoid by not attempting this solution -

  1. Binding directly from a SOQL query as with accounts.
  2. Binding to and IN clause.
  3. Directly returning the results of your AggregateResult without assigning to a variable.
  4. A better method of string concatenation.
  5. A more compact if:else syntax for the volume param.
7
  • yes , there is a problem in my IN syntax but I don't know how to fixe it , I tried your proposition but I get another error message: "Only variable references are allowed in dynamic SOQL/SOSL."
    – dev50
    Nov 7, 2022 at 6:44
  • I modified it to ` Filters = Filters + 'AND Sales__r.Account__r.Station_Account__c IN :'+ accString +' '; `
    – dev50
    Nov 7, 2022 at 7:07
  • This is my all query that I use in the inspector and give me the result SELECT Sales__r.Account__r.Name ,CALENDAR_MONTH(Date_technicalfield__c) Month, SUM(Quantity_in_Kg__c) Quantity FROM SalesK__c WHERE Fiscal_Year__c ='2022' AND Sales__r.Account__r.Station_Account__c='0012000002aU8xXAAD' AND Product_Family__c ='light ' GROUP BY Sales__r.Account__r.Name ,Date_technicalfield__c ORDER BY Date_technicalfield__c ASC
    – dev50
    Nov 7, 2022 at 7:11
  • When I used the binding inside the string The error changed and passed to the next line , I get now as an error message ` "unexpected token: Product_Family__c" `
    – dev50
    Nov 7, 2022 at 7:38
  • 1
    Your solution was the first lead I followed, thank you very much.
    – dev50
    Nov 8, 2022 at 8:02
0

This is what solved my problem I added this to my code

String accString ='';

   for( Account c: acc){
    accString = accString +'\''+ c.Id + '\',';
   }
    accString = accString.substring(0, accString.length()-1);

    Filters = Filters + 'AND Sales__r.Account__r.Station_Account__c IN ('+ accString +  ') AND Product_Family__c = \''+ productFamily +'\' ';

You must log in to answer this question.

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