Load Data Warehouse fails with error 'cannot insert NULL into DWH_CMN_MV_LOOKUP'

Article Id: 36902
Status: Published
Updated On: 14-02-2018 07:37
Legacy Id: TEC1791406
Products:
CLARITY PPM FOR ITG , Clarity PPM On Premise , CLARITY PPM FEDERAL , Clarity PPM SaaS , Clarity PPM SaaS - Application , Clarity PPM On Premise
Issue/Introduction:

Symptom:

For multi-valued lookups if you remove a value from an instance so now it is blank, the Load Data Warehouse job will fail with error:

"cannot insert NULL into ("SCHEMADWH"."DWH_CMN_MV_LOOKUP"."LOOKUP_VALUE_KEY")"      

Steps to Reproduce:

  1. Create a static lookup 
  2. Add 5 static values 
  3. Create a multi-valued lookup attribute on the investment object 
  4. Associate the lookup to the lookup you created in Step 1 
  5. Add your new attribute to the Project List Page 
  6. Choose one of the values and click 'Save' for any project instance 
  7. Click the "x" next to the value to remove it. Click 'Save'  
  8. Run job : Load Data Warehouse - Full Load       

Expected Result:  The Load Data Warehouse job completes successfully.

Actual Result: The Load Data Warehouse job fails with the error " cannot insert NULL into ("SCHEMADWH"."DWH_CMN_MV_LOOKUP"."LOOKUP_VALUE_KEY") "

Cause: 

Caused by CLRT-79697

Records are not being removed from associated tables when the multi-valued lookup instance value is removed.  The value needed for the Load Data Warehouse job is now null which cannot be inserted into the DWH.  Therefore the job fails.

Resolution:

Targeted for next major release.

Workaround:

Add a value back to the blank instance or create another value in the lookup such as 'N/A' which can be used in place of the blank values.

You can run this query on the ppm schema to find the null values:  

select * from dwh_odf_multi_valued_lookups_v where lookup_value_key is null

 

 

Environment:

Release: ESPCLA99000-14.3-Clarity-Extended Support Plus
Component: