Clarity PPM: Load Data Warehouse job fails on DWH_TME_ENTRY

Document ID : KB000123080
Last Modified Date : 17/12/2018
Show Technical Document Details
Issue:
Load Data Warehouse is failing with error:

Couldn't execute SQL: ALTER TABLE DWH_TME_ENTRY DROP COLUMN USER_VALUE1_KEY

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "USER_VALUE1_KEY": invalid identifier



    at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
Couldn't execute SQL: ALTER TABLE DWH_TME_ENTRY DROP COLUMN USER_VALUE1_KEY

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "USER_VALUE1_KEY": invalid identifier
Cause:

This error occurs when the lookup associated with any of the timesheet User Values are changed from the OOTB lookup to a custom dynamic lookup.

The column ends up in dwh_meta_columns several times causing duplication.

The original column is originally deleted from the DWH table but since the Clarity table DWH_META_COLUMNS still contains multiple entries of the attribute, the job fails as it cannot find the column on DWH schema to delete it.

Resolution:
Please contact the Support team for assistance.