Finance | Project Operations, Human Resources, ...

How to find duplicate records across cross-company via sql query?

(0) Share

Report

Posted on by Harshal Jain

162

I am trying to find the duplicate records across cross company, and it gives me an error on count keyword. Please help to identify it.

 while select crosscompany : conCompanies
            AccountNum from vendTable group by vendTable.AccountNum
            Having count(vendTable.AccountNum) > 1 order by asc

I have the same question (0)

All responses (10)

Answers (0)

GirishS 27,827 Moderator on at

Like (0)

Report

Hi Harshal,

Have you tried the same with X++ code?

Thanks,

Girish S.

Was this reply helpful? Yes No
Harshal Jain 162 on at

Like (0)

Report

Hi Girish,

I tried this on X++ only. There its giving me the error on count - invalid token

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Can you use only count(AccountNum) instead of count(vendTable.AccountNum).

Thanks,

Girish S.

Was this reply helpful? Yes No
Harshal Jain 162 on at

Like (0)

Report

Its giving me the same error Girish

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Can you tell me what is the meaning of that line "Having count(vendTable.AccountNum) > 1"

If it throws error that means you are not allowed to use count function in that place.

I guess that without that line you will get the desired output.

Thanks,

Girish S.

Was this reply helpful? Yes No
Harshal Jain 162 on at

Like (0)

Report

Hi Girish,

I am looking for all the vendor accounts which have same value accross different legal entities.

Was this reply helpful? Yes No
Suggested answer

GirishS 27,827 Moderator on at

Like (0)

Report

Refer to the below thread.

https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/274321/how-to-find-duplicate-data-in-table?pifragment-96834=1

Thanks,

Girish S.

Was this reply helpful? Yes No
Suggested answer

Mohit Rampal 12,565 Moderator on at

Like (1)

Report
Hi, You can't use count in where condition. There is an alternative which I think we have discussed before, please check this article.

allaboutax.blogspot.com/.../duplicate-records-in-dynamics-365.html

while select crosscompany AccountNum, count(RecId) from vendTable order by vendTable.AccountNum asc group by vendTable.AccountNum { if (vendTable.RecId) > 1) { //Duplicate record } }

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

For that case you can simply give a SQL query in SSMS which saves time. Any issue there?

Was this reply helpful? Yes No
Harshal Jain 162 on at

Like (1)

Report

Hey Mohit,

This worked. Thanks again

Was this reply helpful? Yes No