Hi,

During the ETL process of BI Apps. we encounter error like "create index failed" frequently. This happen due to the duplicate data in DWH. We can delete duplicate data to rescue the ETL.

For example if we are encountering error on duplicate records on W_EMPLOYEE_DS table.

Query to Find duplicate records will be as below. 

select integration_id, datasource_num_id, src_eff_from_dt, count(*)
from w_employee_ds
group by integration_id, datasource_num_id, src_eff_from_dt
having count(*)>1;

and query to delete duplicate records is as below.

DELETE FROM w_employee_ds
WHERE rowid not in
(SELECT MIN(rowid)
FROM w_employee_ds
GROUP BY integration_id, datasource_num_id, src_eff_from_dt);

Regards

Naeem Akhtar