1

Another weird question, is it possible to specify an expression in a group by clause in X++? basically, how can I do the following:

while select InventDim **group by substr(InventDim.inventBatchId,1,3)**
join InventDimId from InventTrans
where InventTrans.inventDimId == Inventdim.inventDimId && InventTrans.ItemId == _itemId
{
MyQty += inventTrans.Qty
}

thanks for your help regards, Thomas

ian_scho
  • 5,906
  • 9
  • 35
  • 51
Thomas Post
  • 535
  • 2
  • 11
  • 27
  • 1
    I'd use a view on the InventDim, with the calculated field **substr(InventDim.inventBatchId,1,3)** in the field list. Then perform the grouping on the view. – ian_scho Jul 25 '12 at 14:56

1 Answers1

1

No, it's not possible - see Select Statement Syntax to understand what is allowed and what isn't. You have to use some workaround, typically to fetch data without grouping and process them by your application code.

Martin Dráb
  • 259
  • 1
  • 7
  • thanks Martin, I solve my problem by doing what you suggested – Thomas Post Sep 11 '12 at 13:41
  • ian_scho's comment is much better - AX developers need to learn how to work with views and groupings (and use calculated view columns if necessary), iterating thousands of InventTrans records in code (each causing further selects and updates) is a performance killer. – Kim Sullivan Oct 04 '16 at 11:23