Load Data Warehouse fails due to duplicates in DWH_META_TABLES
Article ID: 208295
Updated On:
Products
Clarity PPM On Premise
Clarity PPM SaaS
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
Feedback
Yes
No
Powered by
