Load Data Warehouse job failure

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Load DWH job (Partial and Full) fails with the below error

 

ERROR 2022-08-23 20:10:37,743 [Thread-1934025] dwh.event (none:none:none:none) On Conflict - Insert/Update - 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_INPUT_TYPE_CODE (CLARITY_UPDATED_DATE, INPUT_TYPE_CODE, INPUT_TYPE_CODE_ID, INPUT_TYPE_CODE_KEY, IS_ACTIVE, IS_CHARGEABLE, dw_updated_date) SELECT SRC.PRMODTIME AS CLARITY_UPDATED_DATE, SRC.PRNAME AS INPUT_TYPE_CODE, SRC.PREXTERNALID AS INPUT_TYPE_CODE_ID, SRC.PRID AS INPUT_TYPE_CODE_KEY, SRC.PRISOPEN AS IS_ACTIVE, SRC.IS_CHARGEABLE AS IS_CHARGEABLE, TO_TIMESTAMP('23-aug-2022 20:10:37', 'dd-mon-yyyy hh24:mi:ss') AS dw_updated_date FROM DWH_LKP_INPUT_TYPE_CODE_V SRC WHERE 1 = 1 AND SRC.PRMODTIME >= TO_TIMESTAMP('01-jan-1910 12:00:00', 'dd-mon-yyyy hh24:mi:ss') ON CONFLICT (INPUT_TYPE_CODE_KEY) DO UPDATE SET CLARITY_UPDATED_DATE = EXCLUDED.CLARITY_UPDATED_DATE, INPUT_TYPE_CODE = EXCLUDED.INPUT_TYPE_CODE, INPUT_TYPE_CODE_ID = EXCLUDED.INPUT_TYPE_CODE_ID, IS_ACTIVE = EXCLUDED.IS_ACTIVE, IS_CHARGEABLE = EXCLUDED.IS_CHARGEABLE, dw_updated_date = EXCLUDED.dw_updated_date;
    EXCEPTION WHEN OTHERS THEN 
        V_SQLSTATE := SQLSTATE;
        V_SQLERRM := SQLERRM;
        ROLLBACK;
        BEGIN
        V_EXCEPTION := 'ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_LOOKUP_LOAD DWH_LKP_INPUT_TYPE_CODE. ' || V_SQLERRM;
        V_SQL_TEXT := 'INSERT INTO DWH_LKP_INPUT_TYPE_CODE (CLARITY_UPDATED_DATE, INPUT_TYPE_CODE, INPUT_TYPE_CODE_ID, INPUT_TYPE_CODE_KEY, IS_ACTIVE, IS_CHARGEABLE, dw_updated_date) ${SRC_FULL_QUERY_STR} ON CONFLICT (INPUT_TYPE_CODE_KEY) ' ||
                      'DO UPDATE SET CLARITY_UPDATED_DATE = EXCLUDED.CLARITY_UPDATED_DATE, INPUT_TYPE_CODE = EXCLUDED.INPUT_TYPE_CODE, INPUT_TYPE_CODE_ID = EXCLUDED.INPUT_TYPE_CODE_ID, IS_ACTIVE = EXCLUDED.IS_ACTIVE, IS_CHARGEABLE = EXCLUDED.IS_CHARGEABLE, dw_updated_date = EXCLUDED.dw_updated_date';
        INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES ('DWH_LKP_INPUT_TYPE_CODE',V_EXCEPTION,V_SQLSTATE,REPLACE(V_SQL_TEXT,'**',''''));
        END;
        COMMIT;
        RAISE EXCEPTION '%', V_EXCEPTION; 
    END;
    COMMIT;
END $$
LANGUAGE PLPGSQL;
ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_LOOKUP_LOAD DWH_LKP_INPUT_TYPE_CODE. null value in column "input_type_code" of relation "dwh_lkp_input_type_code" violates not-null constraint
  Where: PL/pgSQL function inline_code_block line 21 at RAISE

 

Environment

Release : 16.0.2

Component : Clarity Data Warehouse

Resolution

The Input Type Codes were created via XOG and it had a Blank Names for the codes which was causing the Null exception failures. 

After populating the Names, the job completed successfully.