Load DWH job failing on 'DWH_INV_PICKLIST_POSITION'

book

Article ID: 223404

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse job is failing we see the following error in the log files;

ROLLBACK;
        INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES('DWH_INV_PICKLIST_POSITION',V_SQLERRM,V_SQLCODE,REPLACE(V_SQL_TEXT,'**',''''));
        COMMIT;
        RAISE_APPLICATION_ERROR(-20100,'ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PICKLIST_POSITION). SQLERRM : ' || V_SQLERRM);
    END;

END;

[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

 

 

Cause

This is caused by duplicate records in ODF_ITEM_POSITIONS table.

Environment

Release : 15.9.2

Component : CLARITY DATA WAREHOUSE

Resolution

Remove the duplicate records in ODF_ITEM_POSITIONS table

You can use the following Sql query to identify the duplicate records

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;