Attribute using Lookup CSK_RPT_ENTITY_FISCAL_YEAR Causes DWH Job Failure
Article ID: 256558
Updated On:
Products
Issue/Introduction
When creating a custom attribute using the lookup
CSK_RPT_ENTITY_FISCAL_YEAR the data warehouse full load will fail with the following message:
INFO 2022-12-20 13:50:31,661 [Thread-924100] dwh.event (none:none:none:none) Full load for the lookup DWH_LKP_CSK_RPT_ENTITY_F_00
INFO 2022-12-20 13:50:31,662 [Thread-924100] dwh.event (none:none:none:none) dwh_lookup_load_pg - Starting entry [Full Insert]
ERROR 2022-12-20 13:50:31,719 [Thread-924100] dwh.event (none:none:none:none) Full Insert - An error occurred executing this job entry :
Couldn't execute SQL: DO $$
DECLARE
V_SQL_TEXT text;
V_EXCEPTION varchar(4000);
V_SQLERRM varchar(4000);
V_SQLSTATE varchar(5);
BEGIN
BEGIN
INSERT INTO DWH_LKP_CSK_RPT_ENTITY_F_00 (CLARITY_UPDATED_DATE, CSK_RPT_ENTITY_F_00, CSK_RPT_ENTITY_F_00_KEY, LANGUAGE_CODE, LANGUAGE_CODE_KEY, dw_updated_date) SELECT SRC.LAST_UPDATED_DATE AS CLARITY_UPDATED_DATE, SRC.PNAME AS CSK_RPT_ENTITY_F_00, SRC.PID AS CSK_RPT_ENTITY_F_00_KEY, SRC.LANGUAGE_CODE AS LANGUAGE_CODE, SRC.LANGUAGE_ID AS LANGUAGE_CODE_KEY, TO_TIMESTAMP('20-dec-2022 13:50:31', 'dd-mon-yyyy hh24:mi:ss') AS dw_updated_date FROM DWH_LKP_CSK_RPT_ENTITY_F_00_V SRC WHERE language_code IN (SELECT language_code FROM cmn_languages WHERE is_dw_enabled = 1) AND SRC.LAST_UPDATED_DATE >= TO_TIMESTAMP('01-jan-1910 00:00:00', 'dd-mon-yyyy hh24:mi:ss');
EXCEPTION WHEN OTHERS THEN
V_SQLSTATE := SQLSTATE;
V_SQLERRM := SQLERRM;
ROLLBACK;
BEGIN
V_EXCEPTION := 'ENCOUNTERED EXCEPTION WHILE INSERTING IN DWH_LOOKUP_LOAD DWH_LKP_CSK_RPT_ENTITY_F_00. ' || V_SQLERRM;
V_SQL_TEXT := 'INSERT INTO DWH_LKP_CSK_RPT_ENTITY_F_00 (CLARITY_UPDATED_DATE, CSK_RPT_ENTITY_F_00, CSK_RPT_ENTITY_F_00_KEY, LANGUAGE_CODE, LANGUAGE_CODE_KEY, dw_updated_date) SELECT SRC.LAST_UPDATED_DATE AS CLARITY_UPDATED_DATE, SRC.PNAME AS CSK_RPT_ENTITY_F_00, SRC.PID AS CSK_RPT_ENTITY_F_00_KEY, SRC.LANGUAGE_CODE AS LANGUAGE_CODE, SRC.LANGUAGE_ID AS LANGUAGE_CODE_KEY, TO_TIMESTAMP(**20-dec-2022 13:50:31**, **dd-mon-yyyy hh24:mi:ss**) AS dw_updated_date FROM DWH_LKP_CSK_RPT_ENTITY_F_00_V SRC WHERE language_code IN (SELECT language_code FROM cmn_languages WHERE is_dw_enabled = 1) AND SRC.LAST_UPDATED_DATE >= TO_TIMESTAMP(**01-jan-1910 00:00:00**, **dd-mon-yyyy hh24:mi:ss**)';
INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES ('DWH_LKP_CSK_RPT_ENTITY_F_00',V_EXCEPTION,V_SQLSTATE,REPLACE(V_SQL_TEXT,'**',''''));
END;
COMMIT;
RAISE EXCEPTION '%', V_EXCEPTION;
END;
COMMIT;
END $$
LANGUAGE PLPGSQL;
ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING IN DWH_LOOKUP_LOAD DWH_LKP_CSK_RPT_ENTITY_F_00. duplicate key value violates unique constraint "dwh_lkp_csk_rpt_entity_f_00_u1"
Where: PL/pgSQL function inline_code_block line 20 at RAISE
Environment
16.0.3, 16.1
Cause
The view that feeds the DWH: DWH_LKP_CSK_RPT_ENTITY_F_00_V
Does not contain a unique key. It appears to be a bad join to one of the tables.
The view looks like this:
Where you can see the PID is not unique and the only difference is in the LNAME field.
Resolution
Reported as DE67806, fixed in 16.1.1
Feedback
