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,825 Moderator on at

Like (0)

Report

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

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

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

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,825 Moderator on at

Like (0)

Report

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

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

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

Its giving me the same error Girish

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

Like (0)

Report

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

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

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

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,825 Moderator on at

Like (0)

Report

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

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,563 Moderator on at

Like (1)

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

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

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

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

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

Hey Mohit,

This worked. Thanks again

Was this reply helpful? Yes No