1

I am unable to use a Datetime field in a SOQL query:

SELECT Status FROM Campaign WHERE LastModifiedDate >= Last_Sync__c;

I have a workaround where I create a checkbox formula field which is true when LastModifiedDate > Last_Sync__c and use that in the SOQL WHERE clause.

Is any other workaround without creating an extra field?

| improve this question | |
  • 1
    I believe this "workaround" is the only solution; SOQL does not support WHERE clauses that compare two fields against each other, but rather just a field against a constant (be that a literal or via a binding variable). – Phil W Jul 24 '19 at 9:57
  • There is another workaround without WHERE clause. Just run for-loop after SOQL and make necessary selection comparing fields in if-clause. You are the one who decides which solution is better: the one that requires new field, or the one that takes more time and queries more rows. – Mariia Illarionova Jul 24 '19 at 10:08
  • Given that this is the only clause in the WHERE, I would suggest doing the test in Apex against the results without the WHERE clause isn't a real option, since there is no way to limit the results. To do that you would need to consider making this into a batch process, and then this is only good for up to 50 million rows in total. – Phil W Jul 24 '19 at 10:24
1

Pulling together my comment and that from Marlia, there are basically two options:

  1. Add a formula field as you have suggested. This isn't really a "workaround" as such since what you are trying to do simply isn't supported by SOQL; SOQL only allows fields to be compared with constants, not other fields, be those literal values or binding variables.
  2. Remove the WHERE sub-clause that is to perform the comparison and do that comparison in Apex code against the results. This is workable if the SOQL query has other WHERE sub-clauses that would ensure that the number of returned rows would be manageable without this comparison part. Recall that you are limited to 50000 rows per session/transaction (this being shared across all your queries in that session/transaction). If removing this sub-clause would mean this limit would be broken, you would have to move your query and processing into a batch (thus raising the over-all limit to 50 million rows across the entire batch start/execute/finish flow).
| improve this answer | |
  • I think i should go with creating one extra field, because that would be less complicated. – Pranav Chitrans Jul 24 '19 at 10:32
1

I had the same issue that that "worakround" was the best solution that I found. It was surprising and strange for me too, but things are strange sometimes... SOQL has its strengths and weaknesses.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.