Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

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

(0) ShareShare
ReportReport
Posted on by 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

  • Harshal Jain Profile Picture
    Harshal Jain 162 on at
    RE: How to find duplicate records across cross-company via sql query?

    Hey Mohit,

    This worked. Thanks again

  • Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,587 Super User 2024 Season 1 on at
    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?

  • Suggested answer
    Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    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
            }
        }

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    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.

  • Harshal Jain Profile Picture
    Harshal Jain 162 on at
    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.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    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.

  • Harshal Jain Profile Picture
    Harshal Jain 162 on at
    RE: How to find duplicate records across cross-company via sql query?

    Its giving me the same error Girish

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    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.

  • Harshal Jain Profile Picture
    Harshal Jain 162 on at
    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

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans