Select that returns a maxof and sum of the same field.

(0) Share

Report

Posted on by Community Member Microsoft Employee

I am querying thru the business connector and I need to get the maxof and sum of the same field. Since there is no way to alias in AX is this possible?

Here is my query

Select KEY1, maxof(REALCONSUMP), sum(REALCONSUMP) FROM %1 GROUP BY KEY1,PRODID WHERE PRODID == '{0}'

If I use .get_Field("REALCONSUMP") I get the maxof but I dont know how to get the maxof.

Thanks

Brian

*This post is locked for comments

All responses (2)

Answers (1)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Select that returns a maxof and sum of the same field.

LOL! Good question. I got so caught up in the aggregates I didn't notice I still had that in the group by. Thanks for the answer.
Verified answer

Martin Dráb 230,188 Most Valuable Professional on at

Like (1)

Report

RE: Select that returns a maxof and sum of the same field.

No, it's not possible. AX returns a table buffer; its fields are defined at design time and not at runtime. There is just one RealConsump field.

You need a different approach, such as two queries.

By the way, what's the point of grouping by ProdId if the query is filtered to return data just for a single ProdId?