Dynamic SOQL Query Exception

Question

global String taskQuery = 'SELECT Id, Subject, What.Name, CA_Created_by_name__c, Task_Record__c, Task_Dc__c, WhatId, createdDate FROM TASK  WHERE WhatId != NULL AND createdDate >= :Datetime.now().addMinutes(-350) AND (Task_Dc__c = : GlobalConstant.DISEASE_NAME_A OR Task_Dc__c = : GlobalConstant.DISEASE_NAME_B)';

the below code is execute method

if (runtime == GlobalConstant.A){
    finalTaskQuery = taskQuery + 'AND Task_Record__c IN: userLHJSet AND Subject = :GlobalConstant.TASK_SUBJ_A LIMIT 49999';

    taskListFortypeA = Database.query(finalTaskQuery); 
}

taskListFortypeA is defined as List of Tasks and finalTaskQuery is a string.

it gives an exception as

System.QueryException:unexpected token: ( for the line taskListFortypeA = Database.query(finalTaskQuery);

Answer 1

Dynamic queries can only use simple variable binds. Anything with a dot/period/full-stop in it (e.g. Datetime.now().addMinutes(-350)) will not work with a dynamic query. The error appears to be occurring with the open paren for .now().

You could fix this by extracting those complex variable binds and putting them into variables, e.g.

DateTime sixHoursAgo = Datetime.now().addMinutes(-360);
String query = 'SELECT Id FROM MyObject__c WHERE CreatedDate > :sixHoursAgo';

but honestly, you don't need a dynamic query here. Don't try to be fancy (especially if you don't understand what it is you're doing). Just use a normal, inline SOQL query.

Answer 2

You can't use Apex bind expressions in a dynamic SOQL query:

:Datetime.now().addMinutes(-350)

Instead, format the date/time value:

'... CreatedDate >= ' + DateTime.now().addMinutes(-360).format('YYYY-MM-DD\'T\'KK:mm:ss.SSS\'Z\'') +

Also, bind expressions don't always work if you include a space between the colon and what comes after:

: GlobalConstant.DISEASE_NAME_A

And also, you can't use dot-notation (for the same reason why you can't use Apex expressions:

: GlobalConstant.DISEASE_NAME_A

And you're also missing a space in your dynamic query:

finalTaskQuery = taskQuery + 'AND Task_Record__c IN: userLHJSet AND Subject = :GlobalConstant.TASK_SUBJ_A LIMIT 49999';
                           // ^ missing space before AND

You'll need to make the query calculate the appropriate values and place them into the string:

global String taskQuery = 'SELECT Id, Subject, What.Name, CA_Created_by_name__c, Task_Record__c, Task_Dc__c, '+
  'WhatId, createdDate '+
  'FROM TASK  WHERE WhatId != NULL AND createdDate >= '+ 
  DateTime.now().addMinutes(-360).format('YYYY-MM-DD\'T\'KK:mm:ss.SSS\'Z\'') + 
    ' AND (Task_Dc__c = \'' + GlobalConstant.DISEASE_NAME_A + '\' OR Task_Dc__c = \'' + GlobalConstant.DISEASE_NAME_B+'\')';

Also, you can use IN for a shorter query:

'Task_DC__c IN (\''+GlobalConstant.DISEASE_NAME_A+'\',\'' + GlobalConstant.DISEASE_NAME_B+'\')'