Load Data Warehouse Incremental Fails when using custom attribute with lookup SCH_BROWSE_RESOURCE_BY_CODE
search cancel

Load Data Warehouse Incremental Fails when using custom attribute with lookup SCH_BROWSE_RESOURCE_BY_CODE

book

Article ID: 238649

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

SUMMARY: Using the lookup SCH_BROWSE_RESOURCE_BY_CODE on a custom attribute may cause the Incremental Load Data Warehouse job to fail when a new resource is created and then the incremental job runs.  This appears to be Postgres database only.

Steps to Reproduce: 

  1. On a Postgres db, run a full load of data warehouse to make sure it completes successfully 
  2. Create a new attribute on the Investment Object 
    • Make it a lookup type and use the lookup ID 'SCH_BROWSE_RESOURCE_BY_CODE' as the lookup 
  3. Save and Return 
  4. Create a new resource.  All you need to have is the basic information on New Labor Resource from Home side 
  5. Run Incremental Load of DWH 

Expected Results: The Load Data Warehouse (LDW) job should complete successfully. 

Actual Results: The job failed in Postgres.  DWH_DIM_REFS_MAPPING_SP fails with mismatched data types. ERROR: Encountered exception while updating reference columns for DWH_RES_RESOURCE. The table does populate, but the new resource is not added to DWH_RES_RESOURCE_LN.

Log error: ERROR 2022-03-30 20:38:11,501 [init of Execute SQL For Lookups (pg).0 (Thread-205702)]
dwh.event Execute SQL For Lookups (pg) - An error occurred, processing will be stopped: 
Couldn't execute SQL: CALL DWH_DIM_REFS_MAPPING_SP
 (
  p_dwh_table => 'DWH_RES_RESOURCE',
  p_last_load_date => to_timestamp('2022/03/30 19:12:47', 'yyyy/MM/dd HH24:mi:ss')::TIMESTAMP WITHOUT TIME ZONE
);

ERROR: Encountered exception while updating reference columns for DWH_RES_RESOURCE.
SQLERRM: operator does not exist: character varying = numeric
  Where: PL/pgSQL function dwh_dim_refs_mapping_sp(character varying,timestamp without time zone) line 77 at RAISE

ERROR 2022-03-30 20:38:11,603 [init of Execute SQL For Lookups (pg).0 (Thread-205702)]
dwh.event Execute SQL For Lookups (pg) - Error initializing step [Execute SQL For Lookups (pg)]

Environment

Release : 16.0.0, 16.0.1, 16.0.2 

Component : Clarity Data Warehouse

Cause

DE64636

Workaround: Run the Full Load of the Data Warehouse Job. 

Resolution

Fixed in 16.0.3 

Powered by Wolken Software