0

I am trying to query counts of an custom object records but i also need to filter out the records. so when i am using single quotes for where clause getting error as:

Error: dailyHLAReportDashboardController Compile Error: expecting a right parentheses, found 'Active' at line 6 column 201

I am trying to use below statement in visualforce controller.

Integer HLA_Signed_By_Customer = database.countQuery('Select id from Hardware_Loan_Agreement__c where CreatedDate>=:Date.today().addDays(-5) and CreatedDate<=:Date.today() and Primary_Status__c = 'Active'');

How can I fix this error?

1

2 Answers 2

1

You need to escape your quotes:

'... WHERE ... AND Primary_Status__c = \'Active\''

Or merge in a value:

String active = 'Active';
'... WHERE ... AND Primary_Status__c = :active'
1
Integer HLA_Signed_By_Customer = database.countQuery('Select id from Hardware_Loan_Agreement__c where CreatedDate>=:Date.today().addDays(-5) and CreatedDate<=:Date.today() and Primary_Status__c = 'Active'');

Cannot Merge Apex Methods in Dynamic SOQL

You can't use things like :Date.today().addDays(-5) in Dynamic SOQL. You can only use simple variables. Instead, you would have to calculate the value ahead of time, or use an inline query.

''Active''

You have to "escape" single quotes, so you'd write \'Active\'. You can avoid this by using an inline query.

Not Counting

You can only use Database.countQuery with COUNT, so you need to start off your query as SELECT COUNT() FROM ....

No Need to Calculate Dates in Apex

SOQL provides literals like TODAY and LAST_N_DAYS:5, so you can write "within the last five days" as "CreatedDate = LAST_N_DAYS:5".

Unnecessary filter

CreatedDate <= TODAY is always true; the created date will never be tomorrow or later.


It'd be lot easier if you used an inline query:

Integer HLA_Signed_By_Customer = [SELECT COUNT()
                                  FROM Hardware_Loan_Agreement__c
                                  WHERE CreatedDate = LAST_N_DAYS:5 AND
                                        Primary_Status__c = 'Active'];

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