Load DWH Job Failing On Table DWH_INV_PICKLIST_POSITION

Products

• Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse job fails with error in the BG DWH log:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PICKLIST_POSITION). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 24

OR

ERROR 2022-02-14 13:18:59,529 [Thread-773525] dwh.event DWH - isOracle? - An error occurred executing this job entry : 
Couldn't execute SQL: DECLARE
    V_SQL_TEXT CLOB;
    V_SQLCODE NUMBER;
    V_SQLERRM VARCHAR2(4000);
BEGIN
MERGE INTO DWH_INV_PICKLIST_POSITION TGT
...
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PICKLIST_POSITION). SQLERRM : ORA-00001: unique constraint (PPM_DWH.DWH_INV_PICKLIST_POSITION_U1) violated
ORA-06512: at line 24

 

 

Cause

This is caused by duplicate records in ODF_ITEM_POSITIONS table.

Resolution

Remove the duplicate records in ODF_ITEM_POSITIONS table.

To identify the duplicate records, please run the following SQL and attach the results to the case.
NOTE: Export the results to an .XLS file with column header names.
==============================
a
select context_id,picklist_def_id,object_code,instance_id,count(lookup_id)
from odf_item_positions ip
group by context_id,picklist_def_id,object_code,instance_id
having count(lookup_id)>1;

b
select context_id, picklist_def_id,object_code,instance_id, lookup_id
from odf_item_positions ip
where 
context_id = from_step_a
and picklist_def_id = from_step_a
and object_code = from_step_a
and instance_id = from_step_a