Removing duplicate records
When you make a change in the layers of AX you will be faced with the installtion checklist. I some cases a removal of a layer might cause duplicates of records that can cause error when trying to synchronize the tables.
To solve this problem a removal of the duplicate records is necessary. This can be done with the sql query below:
The query will remove remove the elements that are duplicates depending on the "=" criteria. To ensure that one record is saved the "min(test_inner.recid) <> LedgerBalancesDimTrans.recid" row has been added. It will make sure that the record with the lowest recid will not be deleted.
To solve this problem a removal of the duplicate records is necessary. This can be done with the sql query below:
delete
from LedgerBalancesDimTrans
where exists(
select *
from LedgerBalancesDimTrans as test_inner
where test_inner.dataareaid = LedgerBalancesDimTrans.dataareaid
and test_inner.accountnum = LedgerBalancesDimTrans.accountnum
and test_inner.transdate =LedgerBalancesDimTrans.transdate
and test_inner.periodcode = LedgerBalancesDimTrans.periodcode
and test_inner.dimension = LedgerBalancesDimTrans.dimension
and test_inner.dimension2_ = LedgerBalancesDimTrans.dimension2_
and test_inner.dimension3_ = LedgerBalancesDimTrans.dimension3_
and test_inner.ledgerbalancesvariant = LedgerBalancesDimTrans.ledgerbalancesvariant
and test_inner.systemgeneratedultimo = LedgerBalancesDimTrans.systemgeneratedultimo
group by dataareaid,accountnum,transdate,periodcode,dimension,dimension2_,dimension3_,ledgerbalancesvariant,systemgeneratedultimo
having count(*) > 1
and min(test_inner.recid) <> LedgerBalancesDimTrans.recid
);
The query will remove remove the elements that are duplicates depending on the "=" criteria. To ensure that one record is saved the "min(test_inner.recid) <> LedgerBalancesDimTrans.recid" row has been added. It will make sure that the record with the lowest recid will not be deleted.
Comments
Post a Comment