deleting duplicate data in a table

Finance and Operations/AX Developer Forum
1

HI friends ,

i have a table containing duplicate data, i want to delete duplicate data.

for ex:

table :

C1 C2 C3
a b c
d e f
h i j
a b c
h i j

here i want to write a job that will delete row no. 4 and 5.

Please help!

Thanks

Manish

2

One way is to use a self join

Example:

delete_k delete_k;

delete_k delete_k_join;

delete_from delete_k

exists join delete_k_join

where delete_k_join.d1 == delete_k.d1 &&

delete_k_join.d2 == delete_k.d2 &&

delete_k_join.d3 == delete_k.d3 &&

delete_k_join.RecId != delete_k.RecId;

3

Hi kartik,

i have checked with this code, it delete all the records which has duplicate records. For example if a record is repeated 3 thimes ( 1 main + 2 duplicate) then this code remove all three record. I want to keep only one record and deleting all its duplicates.

thanks in advance!

Manish

4

Your initial ask mean it.

Why the duplicate entries are allowed initially during the insert, you should avoid inserting the duplicate entries either by building an unique index on the fields where you want an unique combination or at code level you should restrict the duplicate entries insertion.

here is a work around to delete the existing entries,

delete_k delete_k;

delete_k delete_k_select;

delete_k delete_k_delete;

while select delete_k group by

d1, d2, d3

{

select firstOnly RecId from delete_k_select

where delete_k_select.d1 == delete_k.d1 &&

delete_k_select.d2 == delete_k.d2 &&

delete_k_select.d3 == delete_k.d3;

delete_from delete_k_delete

where delete_k_delete.d1 == delete_k.d1 &&

delete_k_delete.d2 == delete_k.d2 &&

delete_k_delete.d3 == delete_k.d3 &&

delete_k_delete.RecId != delete_k_select.RecId;

}

5

I am currently working on a customization on the Sales Order Report using the SSRS.

The report shows the Sales Order details. These details originates from SalesTable and SaleLine.

The report also shows the quantity of items that are on-hand. This come from the InventSum table.

However, multiple data are shown on the form.

As an example:

  1. As per Sales Order, the number of items ordered is set to 5.

  2. Available quantity of the item is 10.

  3. In the Customized Report, the following appears:

17 lines are shown with Order Qty of 5.

ItemId Description Warehouse Location Site

0101 AH20M900SB Big B-1-1 1

Order Qty Available Qty

5

5 -5

5 1

5 1

5 1

5 1

5 1

5 1

5 1

5 1

5 1

5 1

5 1

5 1

5 1

5 1

5 1

10