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.

    Output:

    image

    My Test to see if the data has been inserted:

    image (1)

    I get blanks or no output for above test.

    image (2)

    My Source Tables have data:

    image (3)

     

     

  • This does not appear to be a SQL Server post.

    Also, is it a blog post, or do you have a question to ask?

    If you expect a coded answer, please provide DDL, INSERT statements and desired results.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Looks like it is PostgreSQL script with pgAdmin tool screenshots.

    =======================================================================

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply