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.)

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 '14 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. Apr 4 '14 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 '14 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. Apr 4 '14 at 20:46
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.

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.