Select Group by and Join Order By

Take care if you mix Group By and Order By in select statements. Queries like that will bring you data from the Data Base, but you loose data; Data retrieved form tables in Order By mode will not be available.

The code example below retrieves the desired due dates from table CustTransOpen, but the vouchers from table CustTrans are missing:

CustTable custTable;
CustTrans custTrans;
CustTransOpen custTransOpen;
;

while select CustGroup from custtable 
    group by CustGroup // group by
    join Voucher 
    from custTrans // order by
         where custTrans.AccountNum 
             == custtable.AccountNum
         join DueDate from custTransOpen 
             group by DueDate // group by
             where custTransOpen.RefRecId 
                 == custTrans.RecId
{
    info(custTable.CustGroup); // works
    info(custTrans.Voucher); // works not
    info(Date2StrUsr(custTransOpen
        .DueDate)); // works
}

Add a 'group by' to CustTrans, and you get CustTrans data too:

CustTable custTable;
CustTrans custTrans;
CustTransOpen custTransOpen;
;

while select CustGroup from custtable 
    group by CustGroup // group by
    join Voucher, RecId from custTrans 
        group by Voucher, RecId // group by
        where custTrans.AccountNum 
            == custtable.AccountNum
        join DueDate from custTransOpen 
            group by DueDate // group by
            where custTransOpen.RefRecId 
                == custTrans.RecId
{
    info(custTable.CustGroup); // works
    info(custTrans.Voucher); // works now
    info(Date2StrUsr(custTransOpen
        .DueDate)); // works
}

This issue applies to queries proceeded with a QueryRun object too.

Still not clear, if the source of this behaviour is driven by AX or by the SQL engine. But fact is, the mix of Group By and Order By raises hard finding bugs.