Convert SQL statement to X++

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hi,

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_

Thanks,

Hari

2

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?

3

Hi Martin,

Sorry, It is AX 2012.

Yes. I struggle only with count(distinct transactionid) based on the column field.

Thanks,

Hari

4

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.