ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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

book

Article ID: 203856

calendar_today

Updated On:

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.

Cause

Custom lookup query needs to be updated

Environment

Release : Any

Component : CLARITY DATA WAREHOUSE

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