Load Data Warehouse fails due to duplicates in DWH_META_TABLES

book

Article ID: 208295

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS STARTER PACK-CLARITY PPM

Issue/Introduction

Load Data Warehouse - Full Load fails with error:

2021/02/10 19:00:34 - postgres? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry : 
2021/02/10 19:00:34 - postgres? - Couldn't execute SQL: INSERT INTO DWH_META_TABLE
2021/02/10 19:00:34 - postgres? - SELECT UPPER(A.DWH_TABLE_NAME) DWH_TABLE,
2021/02/10 19:00:34 - postgres? -        UPPER(A.DWH_KEY_COLUMN) DWH_KEY_COL,
2021/02/10 19:00:34 - postgres? -        UPPER(A.DWH_DISPLAY_COLUMN) DWH_DISPLAY_COL,
2021/02/10 19:00:34 - postgres? -        UPPER(A.DWH_DATE_COLUMN) DWH_DATE_COL,
2021/02/10 19:00:34 - postgres? -        A.MULTI_LANG,
2021/02/10 19:00:34 - postgres? -        UPPER(A.SRC_TABLE_NAME) SRC_TABLE,
2021/02/10 19:00:34 - postgres? -        CASE WHEN UPPER(A.DWH_TABLE_NAME) = 'DWH_LKP_TRANSLATION' THEN 'COP_PORTLET_QUERY_TRANSLATIONS' ELSE B.LOOKUP_TYPE END STATIC_LOOKUP_TYPE,
2021/02/10 19:00:34 - postgres? -        UPPER(A.SRC_KEY_COLUMN) SRC_KEY_COL,
2021/02/10 19:00:34 - postgres? -        UPPER(A.SRC_DISPLAY_COLUMN) SRC_DISPLAY_COL,
2021/02/10 19:00:34 - postgres? -        UPPER(A.SRC_DATE_COLUMN) SRC_DATE_COL,
2021/02/10 19:00:34 - postgres? -        A.IS_LOOKUP,
2021/02/10 19:00:34 - postgres? -        A.LOOKUP_SRC_TYPE LOOKUP_TYPE,
2021/02/10 19:00:34 - postgres? -        UPPER(A.DWH_AUDIT_DATE_REF) DWH_AUDIT_DATE,
2021/02/10 19:00:34 - postgres? -        IS_DELETED DROP_FLAG,
2021/02/10 19:00:34 - postgres? -        CASE WHEN UPPER(A.DWH_TABLE_NAME) LIKE UPPER('%_LN') THEN 1 ELSE 0 END AS IS_EXTEND,
2021/02/10 19:00:34 - postgres? -        A.IS_FACT,
2021/02/10 19:00:34 - postgres? -        UPPER(A.DWH_DIM_TABLE_NAME) DWH_DIM_TABLE_NAME,
2021/02/10 19:00:34 - postgres? -        A.HAS_EXTENSION_TABLE
2021/02/10 19:00:34 - postgres? - FROM   DWH_META_TABLES A 
2021/02/10 19:00:34 - postgres? -        LEFT OUTER JOIN (SELECT DISTINCT UPPER(LKP_DIM_TABLE_NAME) LKP_DIM_TABLE_NAME, MAX(LOOKUP_TYPE) OVER (PARTITION BY LKP_DIM_TABLE_NAME) LOOKUP_TYPE 
2021/02/10 19:00:34 - postgres? -                         FROM   DWH_META_COLUMNS
2021/02/10 19:00:34 - postgres? -                         WHERE  LKP_DIM_TABLE_NAME IS NOT NULL) B ON UPPER(A.DWH_TABLE_NAME) = B.LKP_DIM_TABLE_NAME
2021/02/10 19:00:34 - postgres? - ON CONFLICT (DWH_TABLE, SRC_TABLE) 
2021/02/10 19:00:34 - postgres? - DO UPDATE SET DWH_KEY_COL = EXCLUDED.DWH_KEY_COL,
2021/02/10 19:00:34 - postgres? -               DWH_DISPLAY_COL = EXCLUDED.DWH_DISPLAY_COL,
2021/02/10 19:00:34 - postgres? - 			  DWH_DATE_COL = EXCLUDED.DWH_DATE_COL,
2021/02/10 19:00:34 - postgres? -               MULTI_LANG = EXCLUDED.MULTI_LANG,
2021/02/10 19:00:34 - postgres? -               STATIC_LOOKUP_TYPE = EXCLUDED.STATIC_LOOKUP_TYPE,
2021/02/10 19:00:34 - postgres? -               SRC_KEY_COL = EXCLUDED.SRC_KEY_COL,
2021/02/10 19:00:34 - postgres? -               SRC_DISPLAY_COL = EXCLUDED.SRC_DISPLAY_COL,
2021/02/10 19:00:34 - postgres? -               SRC_DATE_COL = EXCLUDED.SRC_DATE_COL,
2021/02/10 19:00:34 - postgres? -               IS_LOOKUP = EXCLUDED.IS_LOOKUP,
2021/02/10 19:00:34 - postgres? -               LOOKUP_TYPE = EXCLUDED.LOOKUP_TYPE,
2021/02/10 19:00:34 - postgres? -               DWH_AUDIT_DATE = EXCLUDED.DWH_AUDIT_DATE,
2021/02/10 19:00:34 - postgres? -               DROP_FLAG = EXCLUDED.DROP_FLAG,
2021/02/10 19:00:34 - postgres? -               IS_EXTEND = EXCLUDED.IS_EXTEND,
2021/02/10 19:00:34 - postgres? -               IS_FACT = EXCLUDED.IS_FACT,
2021/02/10 19:00:34 - postgres? -               DWH_DIM_TABLE_NAME = EXCLUDED.DWH_DIM_TABLE_NAME,
2021/02/10 19:00:34 - postgres? -               HAS_EXTENSION_TABLE = EXCLUDED.HAS_EXTENSION_TABLE
2021/02/10 19:00:34 - postgres? - 
2021/02/10 19:00:34 - postgres? - ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
2021/02/10 19:00:34 - postgres? -   Hint: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Environment

Release : Any

Component : CLARITY PPM DATA WAREHOUSE

Resolution

Engage the DBA team to remove the duplicates from the tables