SQL with group by and order by clause not working as expected

Finance and Operations/AX Developer Forum
1

Hi all. I’m trying to create what I thought would be a reasonably simple SQL statement in X++, but there’s one bit that I can’t get working.

First, in regular SQL, it would look like this:

select parmid, createdby, max(createddatetime), COUNT(RecId)
from SALESPARMTABLE
where DATAAREAID = ‘xx’
and ORDERING = 4 --picking list
group by parmid, createdby
having COUNT(RecId) > 1
order by max(createddatetime) desc

The general idea is to get a summary of all pick list batch runs, then order them by date, descending.

That works fine. In X++, I’m translating it like this:

while select ParmId, CreatedBy, maxOf(CreatedDateTime), count(RecId)
from salesParmTable
group by salesParmTable.ParmId, salesParmTable.CreatedBy
order by salesParmTable.CreatedDateTime desc // doesn’t work.
where salesParmTable.Ordering == DocumentStatus::PickingList
{
if (salesParmTable.RecId < 2)
continue;
nRows++;
// do something with the row here.

}

So the part that doesn’t work is the “order by” clause. The records are returned in seemingly random order.

If I look at ‘salesParmTable.CreatedDateTime’ in the loop, it does seem to be returning the max date/time correctly, it just seems like there’s no way to order on an aggregate field. Is that correct, or am I missing something? If there’s no way to order by an aggregate field, I guess I’m going to have to jump through some hoops to sort the data after retrieving it.

Thanks!

2

I may have answered my own question… I came across a reference (in the AX 4.0 docs) indicating that you can’t have an explicit ‘order by’ clause when you have a ‘group by’ clause. It said that AX would implicitly order the results based on the ‘group by’ clause. I’m not sure if that reference is still supposed to be true for AX 2012, but if it is, then I guess I can’t order by an aggregated field, though it seems like I should get a compiler error, if that was the case.

3

AX2012 does support group by and order by in the same statement, but I guess it doesn’t support ordering by aggregated columns.

I strongly recommend you to check the generated query string - it’s much more useful than depending just on query results. It’s pretty simple in AX2012:

SalesParmTable salesParmTable;
    
select generateOnly ParmId, CreatedBy, maxOf(CreatedDateTime), count(RecId)
    from salesParmTable
    group by salesParmTable.ParmId, salesParmTable.CreatedBy
    order by salesParmTable.CreatedDateTime desc
    where salesParmTable.Ordering == DocumentStatus::PickingList;
    
info(salesParmTable.getSQLStatement());

You’ll get:

SELECT MAX(T1.CREATEDDATETIME),COUNT(T1.RECID),T1.PARMID,T1.CREATEDBY
FROM SALESPARMTABLE T1
WHERE ((DATAAREAID=?) AND (ORDERING=?))
GROUP BY T1.PARMID,T1.CREATEDBY

But if you try ordering by CreatedBy, it works:

select generateOnly ParmId, CreatedBy, maxOf(CreatedDateTime), count(RecId)
    from salesParmTable
    group by salesParmTable.ParmId, salesParmTable.CreatedBy
    order by salesParmTable.CreatedBy desc
    where salesParmTable.Ordering == DocumentStatus::PickingList;

→

SELECT MAX(T1.CREATEDDATETIME),COUNT(T1.RECID),T1.PARMID,T1.CREATEDBY
FROM SALESPARMTABLE T1
WHERE ((DATAAREAID=?) AND (ORDERING=?))
GROUP BY T1.PARMID,T1.CREATEDBY
ORDER BY T1.CREATEDBY DESC
4

Thanks Martin! I think, at some point in the past, I’d stumbled across ‘generateOnly’ / ‘getSQLStatement()’, but I’d completely forgotten about it. I do wish AX/X++ was a little better about letting you use the full power of SQL. Or at least letting you know when it’s going to just ignore your ‘order by’ clause, with a compiler warning or something… [8-)]