No Data Being Inserted

[Anthony Apollis <anthony.apollis@xxxxxxxxx>]

I have a table that i populate using joins.

One of the tables don't insert its data. Tables are:

-- Table: model.IMETA_ZTRB_BRACS_Model_TA_BW

-- DROP TABLE IF EXISTS model."IMETA_ZTRB_BRACS_Model_TA_BW";

CREATE TABLE IF NOT EXISTS model."IMETA_ZTRB_BRACS_Model_TA_BW"
(
    "ZTBR_TransactionCode" integer NOT NULL,
    "Company_Code" character varying COLLATE pg_catalog."default",
    "Posting_Period" text COLLATE pg_catalog."default",
    "Fiscal_Year" text COLLATE pg_catalog."default",
    "Profit_Center" text COLLATE pg_catalog."default",
    "Account_Number" integer,
    "Business_Process" character varying COLLATE pg_catalog."default",
    "Internal_Order" text COLLATE pg_catalog."default",
    "Amount_in_Company_Code_Currency" numeric,
    "Company_Code_Currency" text COLLATE pg_catalog."default",
    "BRACS_FA" character varying COLLATE pg_catalog."default",
    "Expense_Type" text COLLATE pg_catalog."default",
    "BRACS_ACCT_Key" character varying COLLATE pg_catalog."default",
    "CC_Direct" text COLLATE pg_catalog."default",
    "Segment_PC" text COLLATE pg_catalog."default",
    "CC_Master_FA" text COLLATE pg_catalog."default",
    "Region_Secondary_Key" integer,
    "Direct_Indirect_Secondary_Key" integer,
    "Source_Description_Secondary_Key" integer,
    "Entity_Secondary_Key" integer,
    "Master_BRACS_Secondary_Key" integer,
    "Loaddate" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    "Primary_ZTBR_TransactionCode" integer,
    "Acct Type" character varying COLLATE pg_catalog."default",
    "Level 1" character varying COLLATE pg_catalog."default",
    "Level 2" character varying COLLATE pg_catalog."default",
    "Level 3" character varying COLLATE pg_catalog."default",
    "GCoA" integer,
    "Account Desc" text COLLATE pg_catalog."default",
    "EXPENSE FLAG" text COLLATE pg_catalog."default",
    "BRACS" integer,
    "BRACS_DESC" character varying COLLATE pg_catalog."default",
    "CLASSIFICATION" character varying COLLATE pg_catalog."default",
    "Direct_Primary_Key" integer,
    "Entity Name" character varying COLLATE pg_catalog."default",
    "Entity Level" integer,
    "Level 4" character varying COLLATE pg_catalog."default",
    "Level 5" character varying COLLATE pg_catalog."default",
    "Level 6" character varying COLLATE pg_catalog."default",
    "Sub Region" character varying COLLATE pg_catalog."default",
    "Region" character varying COLLATE pg_catalog."default",
    "Consul" character varying COLLATE pg_catalog."default",
    "Report" character varying COLLATE pg_catalog."default",
    "Region BRACS" character varying COLLATE pg_catalog."default",
    "Group" character varying COLLATE pg_catalog."default",
    "Group BRACS" character varying COLLATE pg_catalog."default",
    "BRACS_Key" integer,
    "Function" character varying COLLATE pg_catalog."default",
    "Lead_BRACS_FA" text COLLATE pg_catalog."default",
    "Lead_Classification" text COLLATE pg_catalog."default",
    "Lead_Order" integer,
    CONSTRAINT "ZTBR_TransactionCode_unique" UNIQUE ("ZTBR_TransactionCode")
)

TABLESPACE pg_default;

AND

-- Table: dim.IMETA_Source_Description_Mapping_Lead

-- DROP TABLE IF EXISTS dim."IMETA_Source_Description_Mapping_Lead";

CREATE TABLE IF NOT EXISTS dim."IMETA_Source_Description_Mapping_Lead"
(
    "BRACS_FA" text COLLATE pg_catalog."default",
    "Classification" text COLLATE pg_catalog."default",
    "Order" integer NOT NULL,
    CONSTRAINT "IMETA_Source_Description_Mapping_Lead_pkey" PRIMARY KEY ("Order"),
    CONSTRAINT unique_classification UNIQUE ("Order")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Source_Description_Mapping_Lead"
   

The code i am using:

-- Perform the UPSERT
WITH source_data AS (
    SELECT
        fact."ZTBR_TransactionCode",
        -- New columns from IMETA_Source_Description_Mapping_Lead with "Lead_" prefix to avoid conflict
        lead_map."BRACS_FA" AS "Lead_BRACS_FA",
        lead_map."Classification" AS "Lead_Classification",
        lead_map."Order" AS "Lead_Order"
    FROM
        fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
    LEFT JOIN dim."IMETA_BRACS_Mapping" AS bracs_map
        ON fact."Account_Number"::text = bracs_map."GCoA"::text AND fact."Expense_Type"::text = bracs_map."EXPENSE FLAG"::text
    LEFT JOIN dim."IMETA_Direct_Indirect_Mapping_New" AS direct_indirect_map
        ON bracs_map."Account Desc" = direct_indirect_map."BRACS Account Description"
    LEFT JOIN dim."IMETA_Source_Description_Mapping_Lead" AS lead_map
        ON direct_indirect_map."CLASSIFICATION" = lead_map."Classification"
),
filtered_source_data AS (
    SELECT DISTINCT ON ("ZTBR_TransactionCode") *
    FROM source_data
    ORDER BY "ZTBR_TransactionCode"
)
-- Insert new records or update existing ones
INSERT INTO model."IMETA_ZTRB_BRACS_Model_TA_BW" (
    "ZTBR_TransactionCode",
    "Lead_BRACS_FA",
    "Lead_Classification",
    "Lead_Order"
)
SELECT
    "ZTBR_TransactionCode",
    "Lead_BRACS_FA",
    "Lead_Classification",
    "Lead_Order"
FROM filtered_source_data
ON CONFLICT ("ZTBR_TransactionCode")
DO UPDATE SET
    "Lead_BRACS_FA" = EXCLUDED."Lead_BRACS_FA",
    "Lead_Classification" = EXCLUDED."Lead_Classification",
    "Lead_Order" = EXCLUDED."Lead_Order";

Output:

My Test to see if the data has been inserted:

I get blanks or no output for above test.

My Source Tables have data:

Can't seem to figure out where the issue is.