7

I have some code that does a series of "group by" queries, and uses a common method (apply) to process the results:

for (AggregateResult ar : [
        select PaymentSpecification__c id, Status__c status, sum(Net__c) net
        from Payment__c
        where PaymentSpecification__c in :psIds
        group by PaymentSpecification__c, Status__c
        ]) {
    apply(ar);
}
for (AggregateResult ar : [
        select PaymentSpecification__c id, sum(Net__c) net
        from UnallocatedPayment__c
        where PaymentSpecification__c in :psIds
        group by PaymentSpecification__c
        ]) {
    apply(ar);
}

In some cases one of the result values "status" is not present in the data. Ideally I would like that value to go through into the method as null.

At the moment I am not setting the value (as shown in the second query) and get this runtime error:

Invalid field status for AggregateResult

Is there any way of injecting a constant (and ideally null) to take the place of real data using the alias "status" in this situation?

(Obviously I can code around this; just want to know if there is any SOQL syntax available that helps.)

Improve this question
4
  • This is a very good query, Keith. The alias kind of thing actually would work in ms sql server, mysql. i tried sometime before trying to use a alias but ended in an error...i too waiting for any simple way to achieve without coding...
    – Bforce
    Apr 4, 2014 at 19:20
  • Entirely not the answer you want: but I find it very strange to see "status" "null" possible in your data. To me that implies you're lacking statusses to truly reflect your full process. Setting a default status for new records and an alternative value for records outdating the status field would make the most sense to me.
    – Samuel De Rycke
    Apr 4, 2014 at 20:25
  • @SamuelDeRycke Payment is a child of UnallocatedPayment and the status is managed on the child object. The question is general, as this pattern helps me in a couple of places.
    – Keith C
    Apr 4, 2014 at 20:31
  • that's why besides a comment, I also did an upvote :P - I'm curious, but don't think there's a way.
    – Samuel De Rycke
    Apr 4, 2014 at 20:46

1 Answer 1

Reset to default
0

No, unfortunately you cannot inject a constant in the SOQL query, and you already mentioned you could code around it, but if status is null it should already accomplish what you're requesting.

In your apply method, you'll eventually be doing ar.get('status'), which will return 'null' when it's missing from the data.

Improve this answer

You must log in to answer this question.

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