Load DWH Job Failing during MVL Load with ORA-01427: single-row subquery returns more than one row
search cancel

Load DWH Job Failing during MVL Load with ORA-01427: single-row subquery returns more than one row

book

Article ID: 187454

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

DWH Full load and incremental load fail  during execution DWH_MVL_LOAD with the following error:

DWH_CMN_MV_LOOKUP ORA-01427: single-row subquery returns more than one row

STEPS TO REPRODUCE: 
1. Log in To Clarity
2. Navigate to Administration -> Data Administration -> Lookups.
3. Create a Dynamic lookup with the below query which supports multiple languages.
4. Navigate to Administration -> Studio -> Objects.
5. Search for Project Object.
6. Click on Project Object and navigate to Attributes.
7. Create an Attribute of Type Multi Value Lookup with the lookup created on Step 3 and include it in Data warehouse.
8. Create another attribute of Type Multi Value Lookup with the lookup created on Step 3 and include it in Data warehouse.
9. Add both the attributes to Project - Edit views.
10. Select the value of 'admin' for both the attributes for a single project.
11. Run the Load Data warehouse Job - Full Load.

Detailed example of the error you may see in the bg-dwh.logs:

2020/07/27 12:47:38 - ClarityDB - isOracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_MVL_LOAD (DWH_CMN_MV_LOOKUP). SQLERRM : ORA-01427: single-row subquery returns more than one row
2020/07/27 12:47:38 - ClarityDB - isOracle? - ORA-06512: at line 30
null

Environment

Release : All Supported Releases

Component : CA PPM SAAS DATA WAREHOUSE

Cause

  • This issue was reviewed as DE54074 and determine to be due to the NSQL not being formed properly.
  • One known cause is if the same lookup is used for different attributes in the system, then the job is unable to get a stable set of rows while updating those records in DWH_CMN_MV_LOOKUP table.

Resolution

  1. Create multiple lookups using the same dynamic query with different hidden keys and associate them to different attributes.
  2. If those attributes are not critical for reporting, (or as a temporary fix while correcting the Dynamic Query):
    • Exclude the attributes from the Data warehouse
    • Re-run the Full Load Data warehouse Job.

Additional Information

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

Powered by Wolken Software