2

I'm trying to find the value of a specific field at the end of the month on the month someone subscribed.

I'm having trouble with the where clause in the subquery. Adding a function on a field from the parent object in the where clause (e.g. CALENDAR_MONTH(Account.Subscription_Date__c)) causes the function to break.

Is there some other method I'm supposed to use to refer to a field in the parent object in a relationship subquery?

Here's the query:

select a.Id, a.Subscription_Date__c
(select CreatedDate, NewValue, Account.Subscription_Date__c 
 from histories 
 where Field = 'Plan_Value_Adjusted__c' 
 and CALENDAR_MONTH(CreatedDate) = CALENDAR_MONTH(Account.Subscription_Date__c)
 and CALENDAR_YEAR(CreatedDate) = CALENDAR_YEAR(Account.Subscription_Date__c)
 order by CreatedDate desc
 LIMIT 1
)
from Account a

I also tried this and it had the same issue. If I remove the references to the parent object from the where clause, it works.

select a.Id, a.Subscription_Date__c
(select CreatedDate, NewValue 
 from histories 
 where Field = 'Plan_Value_Adjusted__c' 
 and CALENDAR_MONTH(CreatedDate) = CALENDAR_MONTH(a.Subscription_Date__c)
 and CALENDAR_YEAR(CreatedDate) = CALENDAR_YEAR(a.Subscription_Date__c)
 order by CreatedDate desc
 LIMIT 1
)
from Account a
3
  • What is the issue, exactly? Any error messages? Commented Jul 14, 2016 at 15:35
  • Did the dynamic query work? It could time out too, but it's another solution to try.
    – Adrian Larson
    Commented Jul 15, 2016 at 19:25
  • I ended up just doing it the way I had been, but limiting the rows returned and running it more often so it doesn't timeout. Commented Jul 20, 2016 at 20:34

2 Answers 2

2

Unfortunately, SOQL doesn't support "field equals field" predicates. You can only specify something like "CALENDAR_MONTH(CreatedDate) = 5". You'd have to be able to make a formula on the child object. As you may already know, the AccountHistory object doesn't support customization. This means that there's no way in the current language to do what you're trying to do. You'll have to query all history records then filter them out in code, or gather together the various values from the parent and build a dynamic SOQL query.

2
  • That's what I did originally, and then looped through the subquery results to find the one I need. That resulted in: Apex CPU time limit exceeded Commented Jul 14, 2016 at 15:41
  • @JoeLeKostaj - perhaps Analytic Snapshots could be used to preserve month end values - much easier to query
    – cropredy
    Commented Jul 14, 2016 at 15:55
1

First examine SOQL SELECT Syntax (only relevant bits included here):

SELECT fieldList [subquery][...]
FROM objectType[,...]
[WHERE conditionExpression]

Next examine Condition Expression Syntax (WHERE Clause).

fieldExpression [logicalOperator fieldExpression2][...]

Now take a look at fieldExpression Syntax (emphasis mine):

A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.


You could do two queries, though. If you are just looking for one Account, you don't need a dynamic query, but for many, you could do something like:

static final String clauseFormat = '(AccountId = {0} AND ' +
    'CALENDAR_MONTH(CreatedDate) = {1} AND ' +
    'CALENDAR_YEAR(CreatedDate) = {2})';
static String getFilter(Account record)
{
    return String.format(clauseFormat, new List<String> {
        record.Id,
        getMonth(record.Subscription_Date__c),
        getYear(record.Subscription_Date__c)
    });
}
static String getMonth(Date input)
{
    return (input == null) ? 'null' : String.valueOf(input.month());
}
static String getYear(Date input)
{
    return (input == null) ? 'null' : String.valueOf(input.year());
}

public static List<AccountHistory> getHistories(List<Account> records)
{
    List<String> filters = new List<String>();
    for (Account record : records) filters.add(getFilter(record));
    String soql = 'SELECT ... FROM AccountHistory WHERE ' + String.join(filters, ' OR ');
    return Database.query(soql);
}
1
  • Realized I missed a closing paren.
    – Adrian Larson
    Commented Jul 15, 2016 at 13:11

You must log in to answer this question.

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