X++ Odd count result

1
votes

I'm experiencing a really odd result when I do a count in X++, something I've not experienced before. I am performing what I thought was a really simply count query, but I can't seem to get the result I am after.

WMSOrderTrans   orderTrans;
WMSOrderTrans   orderTransChk;
;

select count(RecId) from orderTrans group by shipmentid where orderTrans.inventTransRefId == 'XXXXXX';
info(strFmt('Count is %1', orderTrans.RecId));


while select orderTransChk group by shipmentid where orderTransChk.inventTransRefId == 'XXXXXX' {
    info(strFmt('Shipment is %1', orderTransChk.shipmentId));
}

The data set that I am selecting all have only 1 shipmentid, so the first select I am expecting a count of 1, instead I get 4 (which is how many lines for that transrefid exist). If I change the count from 'RecId' to 'ShipmentId', then instead of the count, I get actual shipmentId. I simply want it to return the count of the records, which is what I believe I've asked it to do.

I really can't see what I am missing.

In the while select, I get what I expect (the shipmentid), only 1 infolog message for the loop. This tells me that the group by with the where clause is working, but it doesn't explain why the first count select statement isn't behaving as I would expect.

For reference, this is AX2012 R1 system.

3
axaptamicrosoft-dynamicsdynamics-ax-2012x++
Hopefully you solved this already but if not, your aggregate query (the group by) is looking at how many records have the inventTransRefId you set, and how many then share a shipmentID (even if its just one shipmentID). If you know you have 4 records for one inventTransRefId where do you think they are going when you group them under a ShipmentID? Your query is simply not set up to count how many shipmentIDs are there, but instead how many recIDs (total rows since these are always unique) share an inventTransRefId. - Jeff Meden
Thanks Jeff for pointing out my obvious flaw. I knew there must have been something simple I missed (isn't it always the case). This is a real "slap the back of my head" moment. I was expecting 4 different shipment ids, but that wasn't the case. There were 4 different items on 1 shipment id, hence the "group by shipment" rightfully consolidated. I can't believe I missed that, so simple and obvious, yet I was blind to it. The data never lies, and I simply didn't look close enough. - Rat

3 Answers

1
votes

For anyone who might be interested in knowing my answer, it's tied up with Jeff's response. At the end of the day, I didn't look at data well enough, and the query returned the correct results. I initially thought there were a number of unique shipments, but I was wrong. My expected result was erroneous. There were 4 lines in the file, but the lines were unique for the item, not the shipment. They were all on the same shipment. So really, my own fault, it goes to show that one really needs to look at the data closely.

Thanks to all that responded, greatly appreciated.

0
votes

I would try a database sync, then restarting the AOS. I don't see anything obviously wrong, so it points to bouncing everything.

Try getting the select statement via this method (from memory so check syntax) and then review the query against SQL directly. It uses generateOnly.

select generateOnly count(RecId) from orderTrans group by shipmentid where orderTrans.inventTransRefId == 'XXXXXX';

info(orderTrans.getSQLStatement());
0
votes

If I understand what you try to achieve, you'd like to get something like this SQL query:

select count(distinct shipmentid) from orderTrans 
where inventTransRefId = 'XXXXXX'

The 'distinct' keyword is not available in AX select command. The group by clause will allow you to iterate all distinct values but not to use an aggregate on top of it. You may use a sql connection to push the exact sql command you want.

In AX, aggregate values are stores in the field used: Count(RecId), the count will go in the recid field otherwise the system may need to add new field on the buffer on the fly. I don't think you can aggregate on the group by clause as it's important to have its value.

You can try (I don't have an AX to test it) to use a query:

Query                query = new Query();
QueryRun             queryRun;
QueryBuildDataSource qbd;

qbd = query.addDataSource(tablenum(OrderTrans));
qbd.addRange(fieldNum(OrderTrans, InventTransId)).value("xxxx");
qbd.addSortField(fieldNum(OrderTrans, ShipmentId));
qbd.SortOrder(SortOrder::GroupBy);
queryRun = new QueryRun(query);

info(strfmt("Total Records in Query %1",SysQuery::countTotal(queryRun)));