Load DWH job fails with ORA-01427 for table DWH_CMN_MV_LOOKUP when custom lookup query is used

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse (DWH) job - Full and Incremental fails with an error on DWH_CMN_MV_LOOKUP:

Error message: ORA-01427  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Environment

Release : Any

Component : CLARITY DATA WAREHOUSE

Cause

Custom lookup query needs to be updated

Resolution

1. Look in table DWH_CMN_ERROR_MESSAGE for the full SQL that was run.
2. Identify which lookup causes the error.
3. Look in the Data Warehouse lookup view, it must not have duplicate entries.
4. If you see duplicate entries, the custom lookup query must be corrected before being included in the Data Warehouse. The subquery must return unique results.

Workaround: Before correcting the query, the lookup can be unchecked from the Data Warehouse to allow for the job to run smoothly.

Note: Broadcom Support recommends always testing the inclusion of new attributes to Data Warehouse on a Test environment first.

Additional Information

See also:

• Load DWH failing on custom lookup - finding the attribute to disable