Please help me to convert the below SQL statement to X++. Please also consider the performance side. The main think is that count distinct function.
SELECT COMMENT_, COUNT(DISTINCT TRANSACTIONID) FROM RetailTransactionSalesTrans
WHERE TRANSDATE BETWEEN ‘1/1/2014’ AND ‘1/2/2014’
AND DATAAREAID = ‘TES’
GROUP BY COMMENT_
Please don’t forget to add the tag with your AX version.
Do you struggle only with COUNT(DISTINCT TRANSACTIONID), or do you have any additional problems?
Sorry, It is AX 2012.
Yes. I struggle only with count(distinct transactionid) based on the column field.
X++ doesn’t support distinct, nevertheless you can create a view and use distinct in a computed column. I believe it’s the best workaround available.