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)
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)
group by salesParmTable.ParmId, salesParmTable.CreatedBy
order by salesParmTable.CreatedDateTime desc // doesn’t work.
where salesParmTable.Ordering == DocumentStatus::PickingList
if (salesParmTable.RecId < 2)
// 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.