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!
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.