pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedNo Data Being Inserted
3+ messages / 3 participants
[nested] [flat]
* No Data Being Inserted
@ 2023-10-11 11:46 Anthony Apollis <anthony.apollis@gmail.com>
2023-10-11 12:39 ` Re: No Data Being Inserted Ron <ronljohnsonjr@gmail.com>
2023-10-11 15:14 ` Re: No Data Being Inserted Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 3+ messages in thread
From: Anthony Apollis @ 2023-10-11 11:46 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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: image.png]
My Test to see if the data has been inserted:
[image: image.png]
I get blanks or no output for above test.
My Source Tables have data:
[image: image.png]
Can't seem to figure out where the issue is.
Attachments:
image.png (image/png, 109.6K) download
view image
image.png (image/png, 110.8K) download
view image
image.png (image/png, 81.6K) download
view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: No Data Being Inserted
2023-10-11 11:46 No Data Being Inserted Anthony Apollis <anthony.apollis@gmail.com>
@ 2023-10-11 12:39 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ron @ 2023-10-11 12:39 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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) download
view image
image.png (image/png, 110.8K) download
view image
image.png (image/png, 81.6K) download
view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: No Data Being Inserted
2023-10-11 11:46 No Data Being Inserted Anthony Apollis <anthony.apollis@gmail.com>
@ 2023-10-11 15:14 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2023-10-11 15:14 UTC (permalink / raw)
To: Anthony Apollis <anthony.apollis@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-11 15:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-11 11:46 No Data Being Inserted Anthony Apollis <anthony.apollis@gmail.com>
2023-10-11 12:39 ` Ron <ronljohnsonjr@gmail.com>
2023-10-11 15:14 ` Adrian Klaver <adrian.klaver@aklaver.com>
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