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

Like (0)

Report
Copy link

Link copied!

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
Copy link

Link copied!

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

Like (0)

Report
Copy link

Link copied!

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
Copy link

Link copied!

Its giving me the same error Girish

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

Like (0)

Report
Copy link

Link copied!

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
Copy link

Link copied!

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

Like (0)

Report
Copy link

Link copied!

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
Copy link

Link copied!
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
Copy link

Link copied!

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
Copy link

Link copied!

Hey Mohit,

This worked. Thanks again

Was this reply helpful? Yes No