Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Aggregate Selection in...
Microsoft Dynamics AX (Archived)

Aggregate Selection in X++ Query Not Generating Correct SQL Statement

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I am trying to query a view "ChequeStatusView" which has among its columns Document Number and Transaction Currency Amount. My requirement was to retrieve summation of Transaction Currency Amount grouped by Document Number where the summation of Transaction Currency Amount is greater than zero. To use where clause on an aggregate function I had to use Having clause. To use Having clause in X++ I had to use Query object to define my query rather than writing a select statement directly. I had the following code:

ChequeStatusView chequeStatusView;
Query query;
QueryBuildDataSource ds;
QueryRun qr;

query = new Query();
ds = query.addDataSource(tableNum(ChequeStatusView));
ds.addSelectionField(fieldNum(ChequeStatusView,TransactionCurrencyAmount),SelectionField::Sum);
ds.addSelectionField(fieldNum(ChequeStatusView,DocumentNumber)); 

ds.addGroupByField(fieldNum(ChequeStatusView,DocumentNumber)); 
query.addHavingFilter(ds,fieldStr(ChequeStatusView, TransactionCurrencyAmount),AggregateFunction::Sum).value('>0'); 
query.allowCrossCompany(true); 
qr = new QueryRun(query);


This code was not working as expected as all the records of TransactionCurrencyAmount were always zero and by a simple look at the view this result was incorrect as in reality it should have a value. 

I thought that the having clause is not working properly so I searched and I stumbled on this link where a code was mentioned on how to view the generated SQL statement which is sent to SQL Server. So I printed this 

info(ds.toString());

This was the generation which I got.

SELECT DocumentNumber FROM ChequeStatusView(ChequeStatusView_1) GROUP BY ChequeStatusView.DocumentNumber HAVING ((SUM(TransactionCurrencyAmount)>0))

The aggregate method Sum on Transaction Currency Amount was not found.

Since my SQL syntax is a little rusty I tried to run this code on sql management studio and it only retrieved Document Number column ( I had to remove this bit (ChequeStatusView_1) ).

Just for the sake of trying i switched the order of my addSelectionField methods and placed the Document Number before the aggregate method I got this SQL statement:

SELECT SUM(TransactionCurrencyAmount) FROM ChequeStatusView(ChequeStatusView_1) GROUP BY ChequeStatusView.DocumentNumber HAVING ((SUM(TransactionCurrencyAmount)>0))

From what I understand a that when we add a normal column the previously added aggregate is removed and vise versa.

Is this a bug or this is the way it should work? 

For now I have used a normal select statement with an if condition while i iterate through the results, a very inefficient method as mentioned here. 

Please let me know if there is another way to use having clause and if the scenario I mention above is a bug which is fixed in some update.

Note my Ax is R3 with no CUs installed.

*This post is locked for comments

  • Martin Winkler Profile Picture
    Martin Winkler 119 on at
    RE: Aggregate Selection in X++ Query Not Generating Correct SQL Statement

    Thanks @Oleg, great explanation, helped me in researching the inventory on-hand form.

  • Oleg Abapolov Profile Picture
    Oleg Abapolov on at
    RE: Aggregate Selection in X++ Query Not Generating Correct SQL Statement

    Hi Murtaza,

    thank you for your feedback.

    I tested it once again. It worked. Please find details below.

    Test 1. Original code

    static void TestQuery(Args _args)
{
    Query                   q = new Query();
    QueryBuildDataSource    qbds;
    QueryRun                qr;
    QueryBuildFieldList     qbfl;
    CustSalesOpenLines      custSalesOpenLines;

    qbds = q.addDataSource(tableNum(CustSalesOpenLines));
    qbds.firstOnly(true);

    qbds.addSelectionField(fieldNum(CustSalesOpenLines, LineAmount), SelectionField::Sum);
    qbds.addSelectionField(fieldNum(CustSalesOpenLines, SalesId));

    qbds.addGroupByField(fieldNum(CustSalesOpenLines, SalesId));

    q.addHavingFilter(qbds, fieldStr(CustSalesOpenLines, LineAmount), AggregateFunction::Sum).value('>0');

    qr = new QueryRun(q);
    while(qr.next())
    {
        custSalesOpenLines = qr.get(tableNum(CustSalesOpenLines));
        info(strFmt("%1 %2", custSalesOpenLines.SalesId, custSalesOpenLines.LineAmount));
    }
}

    Infolog message:

    SO-100972 0.00

    T-SQL statement:

    SELECT T1.SALESID FROM CUSTSALESOPENLINES T1 
WHERE (((((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (PARTITION#2=@P3)) AND (DATAAREAID#2=@P4)) AND (PARTITION#3=@P5)) 
GROUP BY T1.SALESID 
HAVING (SUM(LINEAMOUNT)>@P6) ORDER BY T1.SALESID

    SUM(LineAmount) is not selected.

    Test 2. Solution code

    static void TestQuery(Args _args)
{
    Query                   q = new Query();
    QueryBuildDataSource    qbds;
    QueryRun                qr;
    QueryBuildFieldList     qbfl;
    CustSalesOpenLines      custSalesOpenLines;

    qbds = q.addDataSource(tableNum(CustSalesOpenLines));
    qbds.firstOnly(true);

    qbfl = qbds.fields();
    qbfl.addField(fieldNum(CustSalesOpenLines, SalesId));
    qbfl.addField(fieldNum(CustSalesOpenLines, LineAmount), SelectionField::Sum);

    qbds.addGroupByField(fieldNum(CustSalesOpenLines, SalesId));

    q.addHavingFilter(qbds, fieldStr(CustSalesOpenLines, LineAmount), AggregateFunction::Sum).value('>0');

    qr = new QueryRun(q);
    while(qr.next())
    {
        custSalesOpenLines = qr.get(tableNum(CustSalesOpenLines));
        info(strFmt("%1 %2", custSalesOpenLines.SalesId, custSalesOpenLines.LineAmount));
    }
}

    Infolog message:

    SO-100972 30,566.00

    T-SQL statement:

    SELECT SUM(T1.LINEAMOUNT),T1.SALESID FROM CUSTSALESOPENLINES T1 
WHERE (((((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (PARTITION#2=@P3)) AND (DATAAREAID#2=@P4)) AND (PARTITION#3=@P5)) 
GROUP BY T1.SALESID 
HAVING (SUM(LINEAMOUNT)>@P6) ORDER BY T1.SALESID

    SUM(LineAmount) is selected.

    Tested in AX 2012 R2. 

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Aggregate Selection in X++ Query Not Generating Correct SQL Statement

    It worked. Thanks Andrew :D.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Aggregate Selection in X++ Query Not Generating Correct SQL Statement

    Solution of using QueryBuildFieldList did not work. Still the same issue remains.

  • Verified answer
    Andrew Xu Profile Picture
    Andrew Xu 3,877 on at
    RE: Aggregate Selection in X++ Query Not Generating Correct SQL Statement

    Hi Murtaza,

    It's not a bug. You can simply resolve this by moving 'ds.addGroupByField(fieldNum(ChequeStatusView,DocumentNumber));' ahead of 'ds.addSelectionField(fieldNum(ChequeStatusView,DocumentNumber));'. 

  • Suggested answer
    Oleg Abapolov Profile Picture
    Oleg Abapolov on at
    RE: Aggregate Selection in X++ Query Not Generating Correct SQL Statement

    Hi Murtaza,

    It can be a bug, but there is a workaround.

    You can use QueryBuildFieldList instead of ds.addSelectionField():

    QueryBuildFieldList qbfl; qbfl = qs.fields(); qbfl.addField(fieldNum(ChequeStatusView,DocumentNumber)); qbfl.addField(fieldNum(ChequeStatusView,TransactionCurrencyAmount), SelectionField::Sum);

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans