From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: No Data Being Inserted
Date: Wed, 11 Oct 2023 07:39:20 -0500
Message-ID: <0fa68146-3bdc-d692-de0a-3bb4f5bc3bd2@gmail.com> (raw)
In-Reply-To: <CAJyMCYJydY1rgMsPquAwr95xsrLP-YUqcwEMe789U1czpaE=PQ@mail.gmail.com>
References: <CAJyMCYJydY1rgMsPquAwr95xsrLP-YUqcwEMe789U1czpaE=PQ@mail.gmail.com>
On 10/11/23 06:46, Anthony Apollis wrote:
> 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:
>
> image.png
It looks like 50M records were inserted
>
> My Test to see if the data has been inserted:
> image.png
You're excluding records where Lead_Order is NULL.
> 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.
--
Born in Arizona, moved to Babylonia.
Attachments:image.png (image/png, 109.6K) downloadview imageimage.png (image/png, 110.8K) downloadview imageimage.png (image/png, 81.6K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: No Data Being Inserted
In-Reply-To: <0fa68146-3bdc-d692-de0a-3bb4f5bc3bd2@gmail.com>
References: <CAJyMCYJydY1rgMsPquAwr95xsrLP-YUqcwEMe789U1czpaE=PQ@mail.gmail.com> <0fa68146-3bdc-d692-de0a-3bb4f5bc3bd2@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox