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:
It looks like 50M records were inserted
My Test to see if the data has been inserted:
You're excluding records where Lead_Order is NULL.
I get blanks or no output for above test.
My Source Tables have data:
Can't seem to figure out where the issue is.
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.
