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
Release : All Supported Releases
Component : CA PPM SAAS DATA WAREHOUSE
See KB: 141061 for tips on how to optimize use of Self Service for Clarity