Incremental/Full Load DWH Job Fails With Error on table DWH_XXX_XXXXX
search cancel

Incremental/Full Load DWH Job Fails With Error on table DWH_XXX_XXXXX

book

Article ID: 234598

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

The full Load DWH job completes, however the incremental run of the Load DWH job fails with error:

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_XXX_XXXX',
      p_last_load_date => to_timestamp('2022/02/11 23:58:56', '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 2/14/22 3:14 PM 
Execute SQL For Lookups (pg) - Error initializing step [Execute SQL For Lookups (pg)]
Error 2/14/22 3:14 PM 
update_dimensions_with_latest_lookups_pg - Step [Execute SQL For Lookups (pg).0] failed to initialize!

 

Resolution

As per dwh_dim_refs_mapping_sp review,

1. Run SQL:

select * from dwh_lkp_references_v
where lookup_table = '<DWHTABLENAME>'

Obtain the objects referenced.

2. Exclude the object referenced above from being included in the DWH.

3. Run the full Load DWH job.

4. Run the incremental Load DWH job.

5. For each object, one at a time, include it back into the DWH and run the Full and Incremental Load DWH jobs.