Load DWH Job fails with ORA-00904: "LANGUAGE_ID": invalid identifier

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse is failing with the below found in the bg-dwh.logs:

ERROR 2020-07-24 06:34:22,714 [Thread-337028] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: DECLARE
    V_SQL_TEXT CLOB;
    V_SQLCODE NUMBER;
    V_SQLERRM VARCHAR2(4000);
.........................

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06550: line 6, column 275:
PL/SQL: ORA-00904: "LANGUAGE_ID": invalid identifier
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored

ERROR 2020-07-24 06:34:22,726 [dwh_etl_interface_lkp - User Defined Java Class] dwh.event User Defined Java Class - Unexpected error
ERROR 2020-07-24 06:34:22,729 [dwh_etl_interface_lkp - User Defined Java Class] dwh.event User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - <Custom lookup name>
 at Processor.processRow(Processor.java:64)
at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)

Environment

Release : All Supported Releases

Component : CA PPM SAAS DATA WAREHOUSE

Cause

The error ORA-00904: "LANGUAGE_ID": invalid identifier points to an issue with the Language_ID with the referenced lookup, due to it either being missing or in a case (lower case) that the system may not be accepting

Resolution

1. Check to see if the LANGUAGE_ID field is in the query associated with the lookup.
   1. If not, add it to the query.
   2. If it is there, but in lower case, try changing it to capitalized which helped in the following issue: Load DataWarehouse' Job Failing

     2. If the Language translations are not required for your lookup and you only prefer a single language then adding Language_code, Language_id to the lookup is not required.  If you do this action, exclude the attribute from the DWH and run the full load before you update the related query.

Tip: To identify the attribute the lookup is associated with to exclude it from the DWH until you are able to fix the root cause, see: 110060

 

 

Additional Information

See KB: 141061 for tips on how to optimize use of Self Service for Clarity PPM