Thread: No Data Being Inserted

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.

On 10/11/23 04:46, Anthony Apollis wrote:
> I have a table that i populate using joins.
> One of the tables don't insert its data. Tables are:

> My Test to see if the data has been inserted:
> image.png
> I get blanks or no output for above test.
> 
> My Source Tables have data:
> image.png
> Can't seem to figure out where the issue is.


1) Verify that your client is not lying to you, use psql to see if there 
is data in the table.

2) Look at the Postgres log to see what is happening. You would want
log_statement in postgresql.conf to be at least 'mod'.

-- 
Adrian Klaver
adrian.klaver@aklaver.com