Group by in x++

(4) Share

Report

Posted on by ..

1,979

Hi,

I'm getting this error for this group by code

When using qualified field names, statements can contain a maximum of one 'order by' clause and one 'group by' clause. When using unqualified field names, statements can either contain multiple 'order by' clauses or multiple 'group by' clauses

What's wrong with it?


while select FieldA, FieldB from TableA
        group by TableA.FieldA, TableA.FieldB
        where TableA.FieldA == "123"
            && TableA.FieldB == "ABC"
    join sum(FieldC) from TableB 
        where TableB.RefId == TableA.RecId
    join RecId from TableC
        where TableC.LinkId == TableA.LinkId
    join ReferenceField from TableD
        group by TableD.ReferenceField  //if i comment this out the error goes but i need it
        where TableD.LinkId == TableC.LinkId
{
   info("TEST");
}

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (6)

Answers (2)

Verified answer

Jonas "Jones" Melgaard 4,991 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
"Group by" and "Order By" can only be used once in an select statement, if you use the full table name, and should be defined after the "Join" or "From" clause and before the first "Where" clause if any.

This article describes it in details: Select statement - Finance & Operations | Dynamics 365 | Microsoft Learn

So the select should look something like this:

while select FieldA, FieldB from TableA group by TableA.FieldA, TableA.FieldB,TableD.ReferenceField where TableA.FieldA == "123" && TableA.FieldB == "ABC" join sum(FieldC) from TableB where TableB.RefId == TableA.RecId join RecId from TableC where TableC.LinkId == TableA.LinkId join ReferenceField from TableD where TableD.LinkId == TableC.LinkId { info("TEST"); }

Edit: Put emphasis on the syntax is incorrect, if you use tableName as part of you groupBy clause. Details of "should" recommendation is part of the linked docs article.

Was this reply helpful? Yes No
Suggested answer

Navneeth Nagrajan 2,445 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Recommendation would be to use the below mentioned syntax. Correcting suggestion 1.

or

Hope this helps. Happy to answer questions, if any.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 238,281 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
The statement Group by" and "Order By" can only be used once in an select statement is contradicted by the very first sentence of the linked section of documentation: A query can have multiple group by clauses [...]. As the both the error message and the documentioned mentioned, it depends on where the field names are qualified (e.g. group by tableA.FieldA) or not (e.g. group by FieldA).

Let me quote the documentation in full:

A query can have multiple group by clauses, but the fields can be qualified by a table name in only one group by clause. We recommend that you use table name qualifiers. The order by clause follows the same syntax patterns as group by. Both clauses, if they are provided, must appear after the join (or from) clause, and both must appear before any where clause that exists on the same join clause. We recommend that all group by, order by, and where clauses appear immediately after the last join clause. The following example shows a group by clause where a field is qualified by a table name.

This is a variant with a single group by clause and qualified names:

select salesLine group by salesLine.ItemId, salesTable.CustAccount join salesTable where salesTable.SalesId == salesLine.SalesId;

And here multiple group by clauses with unqualified names:

select salesLine group by ItemId join salesTable group by CustAccount where salesTable.SalesId == salesLine.SalesId;

The first code snippet suggested by Navneeth has the same bug as the original one. The second one is possible, nevertheless the usual approach is putting the group by clause on the top.

Was this reply helpful? Yes No
Suggested answer

Navneeth Nagrajan 2,445 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Thanks Jonas for correcting the response. Will edit the first recommendation so that there are no wrong answers to the set of question in the forum.

Re-wrote the group by and this piece works too.

Was this reply helpful? Yes No
.. 1,979 on at

Like (1)

Report
Copy link

Link copied!
Hi All,

Thanks alot for your inputs

So the recommendation is, if we want to group by more than one table, then leave the group by till the last join, right? and not under each table

So it would look like this now:

while select FieldA, FieldB from TableA group by TableA.FieldA, TableA.FieldB, TableD.ReferenceField where TableA.FieldA == "123" && TableA.FieldB == "ABC" join sum(FieldC) from TableB where TableB.RefId == TableA.RecId join RecId from TableC where TableC.LinkId == TableA.LinkId join ReferenceField from TableD where TableD.LinkId == TableC.LinkId { info("TEST"); }

Naveenth you mentioned that i should group by RecId as well in Table C but i think this is wrong as it will ruin the whole group by concept
I think i can join to RecId without affecting the group by

so the above code is correct

Was this reply helpful? Yes No
Navneeth Nagrajan 2,445 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!
Hi,

Its not necessary to leave the group by till the last join.

You can write it like this, as suggested by Jonas. This will work leaving out the RecId.

while select FieldA, FieldB from TableA group by TableA.FieldA, TableA.FieldB, TableD.ReferenceField where TableA.FieldA == "123" && TableA.FieldB == "ABC" join sum(FieldC) from TableB where TableB.RefId == TableA.RecId join RecId from TableC where TableC.LinkId == TableA.LinkId join ReferenceField from TableD where TableD.LinkId == TableC.LinkId { info("TEST"); }

Usually prefer writing the group by in a single line to reduce the number of lines of code. The usual approach, as Martin highlighted is to put the group by statement on the top but the second option does work well too.

Was this reply helpful? Yes No