Can find the issue for join that does not work

Question

Can find the issue for join that does not work

Viewing 13 posts - 16 through 27 (of 27 total)

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

yrstruly Hall of Fame Points: 3475 More actions · Answer 1

Is there a better way in doing this?

Here is the Fact Table:

-- Table: system.IMETA_ZTRB_MP$F_ZTBR_TA_BW

-- DROP TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW";

CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
    "ZTBR_TransactionCode" integer NOT NULL DEFAULT nextval('system."IMETA_ZTBR_TransactionCode_Seq"'::regclass),
    "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" text COLLATE pg_catalog."default",
    "Business_Process" character varying COLLATE pg_catalog."default",
    "Internal_Order" text COLLATE pg_catalog."default",
    "Trading_Partner" 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" date,
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey" PRIMARY KEY ("ZTBR_TransactionCode"),
    CONSTRAINT "IMETA_ZTBR_TransactionCode_unique" UNIQUE ("ZTBR_TransactionCode"),
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_fkey" FOREIGN KEY ("Direct_Indirect_Secondary_Key")
        REFERENCES dim."IMETA_Direct_Indirect_Mapping_New" ("Direct_Primary_Key") MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_fkey" FOREIGN KEY ("Entity_Secondary_Key")
        REFERENCES dim."IMETA_Entity_Mapping" ("Entity_ID") MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_fkey" FOREIGN KEY ("Master_BRACS_Secondary_Key")
        REFERENCES dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_" ("Primary_ZTBR_TransactionCode") MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_fkey" FOREIGN KEY ("Region_Secondary_Key")
        REFERENCES dim."IMETA_Region_Mapping" ("Region_Primary_Key") MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Ke_fkey" FOREIGN KEY ("Source_Description_Secondary_Key")
        REFERENCES dim."IMETA_Source_Description_Mapping" ("BRACS_Key") MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
    OWNER to apollia;

-- Trigger: update_source_description_key_trigger

-- DROP TRIGGER IF EXISTS update_source_description_key_trigger ON system."IMETA_ZTRB_MP$F_ZTBR_TA_BW";

CREATE TRIGGER update_source_description_key_trigger
    BEFORE INSERT OR UPDATE 
    ON system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
    FOR EACH ROW
    EXECUTE FUNCTION public.update_source_description_key();

First Join Dimension:

-- Table: dim.IMETA_BRACS_Mapping

-- DROP TABLE IF EXISTS dim."IMETA_BRACS_Mapping";

CREATE TABLE IF NOT EXISTS dim."IMETA_BRACS_Mapping"
(
    "Primary_ZTBR_TransactionCode" integer NOT NULL DEFAULT nextval('dim."IMETA_BRACS_Mapping_TransactionCode_seq"'::regclass),
    "Acct Type" character varying(255) COLLATE pg_catalog."default",
    "Level 1" character varying(255) COLLATE pg_catalog."default",
    "Level 2" character varying(255) COLLATE pg_catalog."default",
    "Level 3" character varying(255) 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(255) COLLATE pg_catalog."default",
    CONSTRAINT "IMETA_BRACS_Mapping_pkey" PRIMARY KEY ("Primary_ZTBR_TransactionCode")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_BRACS_Mapping"
    OWNER to ;

Im not getting any data return for this join. I did check the data types.

SELECT 
    fact.*,
    bracs_map."Acct Type",
    bracs_map."Level 1",
    bracs_map."Level 2",
    bracs_map."Level 3",
    bracs_map."GCoA",
    bracs_map."Account Desc",
    bracs_map."EXPENSE FLAG",
    bracs_map."BRACS",
    bracs_map."BRACS_DESC"
FROM 
    system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
LEFT JOIN 
    dim."IMETA_BRACS_Mapping" AS bracs_map
ON 
    TRIM(LOWER(fact."Account_Number")) = TRIM(LOWER(bracs_map."Account Desc")) AND
    TRIM(LOWER(fact."Expense_Type")) = TRIM(LOWER(bracs_map."EXPENSE FLAG")) limit 6000;

Second Join:

-- Table: dim.IMETA_Source_Description_Mapping

-- DROP TABLE IF EXISTS dim."IMETA_Source_Description_Mapping";

CREATE TABLE IF NOT EXISTS dim."IMETA_Source_Description_Mapping"
(
    "BRACS_Key" integer NOT NULL DEFAULT nextval('dim."IMETA_Source_Description_Mapping_BRACS_Key_seq"'::regclass),
    "BRACSFA" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    "Function" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "IMETA_Source_Description_Mapping_pkey" PRIMARY KEY ("BRACS_Key")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Source_Description_Mapping"
    OWNER to apollia;

-- Table: dim.IMETA_Direct_Indirect_Mapping_New

-- DROP TABLE IF EXISTS dim."IMETA_Direct_Indirect_Mapping_New";

CREATE TABLE IF NOT EXISTS dim."IMETA_Direct_Indirect_Mapping_New"
(
    "BRACS Account Description" character varying(255) COLLATE pg_catalog."default",
    "CLASSIFICATION" character varying(255) COLLATE pg_catalog."default",
    "Direct_Primary_Key" integer NOT NULL,
    CONSTRAINT "IMETA_Direct_Indirect_Mapping_New_pkey" PRIMARY KEY ("Direct_Primary_Key")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Direct_Indirect_Mapping_New"
    OWNER to apollia;

-- Table: dim.IMETA_Entity_Mapping

-- DROP TABLE IF EXISTS dim."IMETA_Entity_Mapping";

CREATE TABLE IF NOT EXISTS dim."IMETA_Entity_Mapping"
(
    "Entity" character varying(255) COLLATE pg_catalog."default",
    "Entity Name" character varying(255) COLLATE pg_catalog."default",
    "Entity Level" integer,
    "Level 1" character varying(255) COLLATE pg_catalog."default",
    "Level 2" character varying(255) COLLATE pg_catalog."default",
    "Level 3" character varying(255) COLLATE pg_catalog."default",
    "Level 4" character varying(255) COLLATE pg_catalog."default",
    "Level 5" character varying(255) COLLATE pg_catalog."default",
    "Level 6" character varying(255) COLLATE pg_catalog."default",
    "Entity_ID" integer NOT NULL DEFAULT nextval('dim."IMETA_Entity_Mapping_Entity_ID_seq"'::regclass),
    "Loaddate" date,
    CONSTRAINT "IMETA_Entity_Mapping_pkey" PRIMARY KEY ("Entity_ID")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Entity_Mapping"
    OWNER to apollia;

-- Table: dim.IMETA_Region_Mapping

-- DROP TABLE IF EXISTS dim."IMETA_Region_Mapping";

CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping"
(
    "CoCd" character varying(255) COLLATE pg_catalog."default",
    "Sub Region" character varying(255) COLLATE pg_catalog."default",
    "Region" character varying(255) COLLATE pg_catalog."default",
    "BRACS Entity" character varying(255) COLLATE pg_catalog."default",
    "Consul" character varying(255) COLLATE pg_catalog."default",
    "Report" character varying(255) COLLATE pg_catalog."default",
    "Region BRACS" character varying(255) COLLATE pg_catalog."default",
    "Group" character varying(255) COLLATE pg_catalog."default",
    "Group BRACS" character varying(255) COLLATE pg_catalog."default",
    loaddate timestamp without time zone DEFAULT now(),
    "Region_Primary_Key" integer NOT NULL DEFAULT nextval('dim."IMETA_Region_Mapping_Region_Primary_Key_seq"'::regclass),
    CONSTRAINT "IMETA_Region_Mapping_pkey" PRIMARY KEY ("Region_Primary_Key")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Region_Mapping"
    OWNER to apollia;

This reply was modified 1 year, 2 months ago by yrstruly.

yrstruly Hall of Fame Points: 3475 More actions · Answer 2

I have joined two dimension tables here:

SELECT 
    fact."ZTBR_TransactionCode",
    fact."Company_Code",
    fact."Posting_Period",
    fact."Fiscal_Year",
    fact."Profit_Center",
    fact."Account_Number",
    fact."Business_Process",
    fact."Internal_Order",
    fact."Trading_Partner",
    fact."Amount_in_Company_Code_Currency",
    fact."Company_Code_Currency",
    fact."BRACS_FA",
    fact."Expense_Type",
    fact."BRACS_ACCT_Key",
    fact."CC_Direct",
    fact."Segment_PC",
    fact."CC_Master_FA",
    fact."Region_Secondary_Key",
    fact."Direct_Indirect_Secondary_Key",
    fact."Source_Description_Secondary_Key",
    fact."Entity_Secondary_Key",
    fact."Master_BRACS_Secondary_Key",
    bracs_map."Acct Type",
    bracs_map."Level 1",
    bracs_map."Level 2",
    bracs_map."Level 3",
    bracs_map."GCoA",
    bracs_map."Account Desc",
    bracs_map."EXPENSE FLAG",
    bracs_map."BRACS",
    bracs_map."BRACS_DESC",
    bracs_map."Primary_ZTBR_TransactionCode",
    direct_indirect_map."BRACS Account Description",
    direct_indirect_map."CLASSIFICATION",
    direct_indirect_map."Direct_Primary_Key"
FROM 
    system."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"
LIMIT 50;

Here i can start seeing NULL values:

By the 3rd and 4th join the Null values become more visible(i need to filter out NOT NULL if i want to see a full complete view):

SELECT 
    -- Columns from the fact table
    fact."ZTBR_TransactionCode",
    fact."Company_Code",
    fact."Posting_Period",
    fact."Fiscal_Year",
    fact."Profit_Center",
    fact."Account_Number",
    fact."Business_Process",
    fact."Internal_Order",
    fact."Trading_Partner",
    fact."Amount_in_Company_Code_Currency",
    fact."Company_Code_Currency",
    fact."BRACS_FA",
    fact."Expense_Type",
    fact."BRACS_ACCT_Key",
    fact."CC_Direct",
    fact."Segment_PC",
    fact."CC_Master_FA",
    fact."Region_Secondary_Key",
    fact."Direct_Indirect_Secondary_Key",
    fact."Source_Description_Secondary_Key",
    fact."Entity_Secondary_Key",
    fact."Master_BRACS_Secondary_Key",
    
    -- Columns from the first dimension table (dim."IMETA_BRACS_Mapping")
    bracs_map."Acct Type",
    bracs_map."Level 1",
    bracs_map."Level 2",
    bracs_map."Level 3",
    bracs_map."GCoA",
    bracs_map."Account Desc",
    bracs_map."EXPENSE FLAG",
    bracs_map."BRACS",
    bracs_map."BRACS_DESC",
    bracs_map."Primary_ZTBR_TransactionCode",
    
    -- Columns from the second dimension table (dim."IMETA_Direct_Indirect_Mapping_New")
    direct_indirect_map."BRACS Account Description",
    direct_indirect_map."CLASSIFICATION",
    direct_indirect_map."Direct_Primary_Key",
    
    -- Columns from the third dimension table (dim."IMETA_Source_Description_Mapping")
    src_desc_map."BRACS_Key",
    src_desc_map."BRACSFA",
    src_desc_map."Function",
    
    -- Columns from the fourth dimension table (dim."IMETA_Region_Mapping")
    region_map."CoCd",
    region_map."Region",
    region_map."Sub Region",
    region_map."BRACS Entity",
    region_map."Consul",
    region_map."Report",
    region_map."Region BRACS",
    region_map."Group",
    region_map."Group BRACS",
    region_map."Region_Primary_Key"

FROM 
    system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact

-- Join with the first dimension table (dim."IMETA_BRACS_Mapping")
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

-- Join with the second dimension table (dim."IMETA_Direct_Indirect_Mapping_New")
LEFT JOIN 
    dim."IMETA_Direct_Indirect_Mapping_New" AS direct_indirect_map
ON 
    bracs_map."Account Desc" = direct_indirect_map."BRACS Account Description"

-- Join with the third dimension table (dim."IMETA_Source_Description_Mapping")
LEFT JOIN 
    dim."IMETA_Source_Description_Mapping" AS src_desc_map
ON 
    ltrim(fact."BRACS_FA", '0') = src_desc_map."BRACSFA"

-- Join with the fourth dimension table (dim."IMETA_Region_Mapping")
LEFT JOIN 
    dim."IMETA_Region_Mapping" AS region_map
ON 
    fact."Company_Code"::text = region_map."CoCd"::text

LIMIT 50;

The Last Table added:

I believe it is from the 3rd or 4th join that those instructions regarding UNION must take place, please advice?

SELECT 
    -- Columns from the fact table
    fact."ZTBR_TransactionCode",
    fact."Company_Code",
    fact."Posting_Period",
    fact."Fiscal_Year",
    fact."Profit_Center",
    fact."Account_Number",
    fact."Business_Process",
    fact."Internal_Order",
    fact."Trading_Partner",
    fact."Amount_in_Company_Code_Currency",
    fact."Company_Code_Currency",
    fact."BRACS_FA",
    fact."Expense_Type",
    fact."BRACS_ACCT_Key",
    fact."CC_Direct",
    fact."Segment_PC",
    fact."CC_Master_FA",
    fact."Region_Secondary_Key",
    fact."Direct_Indirect_Secondary_Key",
    fact."Source_Description_Secondary_Key",
    fact."Entity_Secondary_Key",
    fact."Master_BRACS_Secondary_Key",
    
    -- Columns from the first dimension table (dim."IMETA_BRACS_Mapping")
    bracs_map."Acct Type",
    bracs_map."Level 1",
    bracs_map."Level 2",
    bracs_map."Level 3",
    bracs_map."GCoA",
    bracs_map."Account Desc",
    bracs_map."EXPENSE FLAG",
    bracs_map."BRACS",
    bracs_map."BRACS_DESC",
    bracs_map."Primary_ZTBR_TransactionCode",
    
    -- Columns from the second dimension table (dim."IMETA_Direct_Indirect_Mapping_New")
    direct_indirect_map."BRACS Account Description",
    direct_indirect_map."CLASSIFICATION",
    direct_indirect_map."Direct_Primary_Key",
    
    -- Columns from the third dimension table (dim."IMETA_Source_Description_Mapping")
    src_desc_map."BRACS_Key",
    src_desc_map."BRACSFA",
    src_desc_map."Function",
    
    -- Columns from the fourth dimension table (dim."IMETA_Region_Mapping")
    region_map."CoCd",
    region_map."Region",
    region_map."Sub Region",
    region_map."BRACS Entity",
    region_map."Consul",
    region_map."Report",
    region_map."Region BRACS",
    region_map."Group",
    region_map."Group BRACS",
    region_map."Region_Primary_Key",
    
    -- Columns from the fifth dimension table (dim."IMETA_Entity_Mapping")
    entity_map."Entity",
    entity_map."Entity Name",
    entity_map."Entity Level",
    entity_map."Level 1",
    entity_map."Level 2",
    entity_map."Level 3",
    entity_map."Level 4",
    entity_map."Level 5",
    entity_map."Level 6",
    entity_map."Entity_ID",
    entity_map."Loaddate"

FROM 
    system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact

-- Join with the first dimension table (dim."IMETA_BRACS_Mapping")
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

-- Join with the second dimension table (dim."IMETA_Direct_Indirect_Mapping_New")
LEFT JOIN 
    dim."IMETA_Direct_Indirect_Mapping_New" AS direct_indirect_map
ON 
    bracs_map."Account Desc" = direct_indirect_map."BRACS Account Description"

-- Join with the third dimension table (dim."IMETA_Source_Description_Mapping")
LEFT JOIN 
    dim."IMETA_Source_Description_Mapping" AS src_desc_map
ON 
    ltrim(fact."BRACS_FA", '0') = src_desc_map."BRACSFA"

-- Join with the fourth dimension table (dim."IMETA_Region_Mapping")
LEFT JOIN 
    dim."IMETA_Region_Mapping" AS region_map
ON 
    fact."Company_Code"::text = region_map."CoCd"::text

-- Join with the fifth dimension table (dim."IMETA_Entity_Mapping")
LEFT JOIN 
    dim."IMETA_Entity_Mapping" AS entity_map
ON 
    region_map."BRACS Entity" = entity_map."Entity"

LIMIT 50;

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 3

You could create a temporary table.

Then insert the rows from each query into the temporary table with a WHERE NOT EXSITS (SELECT * FROM temp_table_name WHERE ...) at the end so you are no duplicating any of the rows.

yrstruly Hall of Fame Points: 3475 More actions · Answer 4

yrstruly

Hall of Fame

Points: 3475

September 19, 2023 at 9:37 am

#4289474

Please give example of this code?

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 5

Create a temporary table with these columns

"ZTBR_TransactionCode",
"Company_Code",
"Posting_Period",
"Fiscal_Year",
"Profit_Center",
"Account_Number",
"Business_Process",
"Internal_Order",
"Trading_Partner",
"Amount_in_Company_Code_Currency",
"Company_Code_Currency",
"BRACS_FA",
"Expense_Type",
"BRACS_ACCT_Key",
"CC_Direct",
"Segment_PC",
"CC_Master_FA",
"Region_Secondary_Key",
"Direct_Indirect_Secondary_Key",
"Source_Description_Secondary_Key",
"Entity_Secondary_Key",
"Master_BRACS_Secondary_Key",
"Acct Type",
"Level 1",
"Level 2",
"Level 3",
"GCoA",
"Account Desc",
"EXPENSE FLAG",
"BRACS",
"BRACS_DESC",
"Primary_ZTBR_TransactionCode",
"BRACS Account Description",
"CLASSIFICATION",
"Direct_Primary_Key"

Insert the rows from the fist query into the temp table

Then insert the rows from the second query into the temp table with a not exists:

INSERT INTO temp_table_name(
)
SELECT ***
FROM 
WHERE NOT EXISTS(SELECT *
                   FROM temp_table_name t
                  WHERE t.PrimaryKeyColumn = myquery.PrimaryKeyColumn)

Do this for all the remaining queries.

yrstruly Hall of Fame Points: 3475 More actions · Answer 6

Does this solve the issue of the NULL columns and would i still need to do the UNION as described in the requirements/tasks?

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 7

yrstruly wrote:

Does this solve the issue of the NULL columns and would i still need to do the UNION as described in the requirements/tasks?

I think it should solve your issues with NULL values. I don't know enough about your data but you might be able to use inner joins instead of left joins so that nulls don't appear.

You will not need to do a UNION as the successive inserts are equivalent to unions.

yrstruly Hall of Fame Points: 3475 More actions · Answer 8

Does the Temp table need to have all the columns from the Join, don't understand where you say "Do this for all the remaining queries."?


SELECT 
    fact."ZTBR_TransactionCode",
    fact."Company_Code",
    fact."Posting_Period",
    fact."Fiscal_Year",
    fact."Profit_Center",
    fact."Account_Number",
    fact."Business_Process",
    fact."Internal_Order",
   
    fact."Amount_in_Company_Code_Currency",
    fact."Company_Code_Currency",
    fact."BRACS_FA",
    fact."Expense_Type",
    fact."BRACS_ACCT_Key",
    fact."CC_Direct",
    fact."Segment_PC",
    fact."CC_Master_FA",
    fact."Region_Secondary_Key",
    fact."Direct_Indirect_Secondary_Key",
    fact."Source_Description_Secondary_Key",
    fact."Entity_Secondary_Key",
    fact."Master_BRACS_Secondary_Key",
    bracs_map."Acct Type",
    bracs_map."Level 1" AS "BRACS_Level_1",
    bracs_map."Level 2" AS "BRACS_Level_2",
    bracs_map."Level 3" AS "BRACS_Level_3",
    bracs_map."GCoA",
    bracs_map."Account Desc",
    bracs_map."EXPENSE FLAG",
    bracs_map."BRACS",
    bracs_map."BRACS_DESC",
    direct_indirect_map."BRACS Account Description",
    direct_indirect_map."CLASSIFICATION",
    direct_indirect_map."Direct_Primary_Key",
    region_map."CoCd",
    region_map."Region",
    region_map."Sub Region",
    region_map."BRACS Entity",
    region_map."Consul",
    region_map."Report",
    region_map."Region BRACS",
    region_map."Group",
    region_map."Group BRACS",
    region_map."Region_Primary_Key",
    entity_map."Entity",
    entity_map."Entity Name",
    entity_map."Entity Level",
    entity_map."Level 1" AS "Entity_Level_1",
    entity_map."Level 2" AS "Entity_Level_2",
    entity_map."Level 3" AS "Entity_Level_3",
    entity_map."Level 4" AS "Entity_Level_4",
    entity_map."Level 5" AS "Entity_Level_5",
    entity_map."Level 6" AS "Entity_Level_6",
    entity_map."Entity_ID",
    src_desc_map."BRACS_Key",
    src_desc_map."BRACSFA",
    src_desc_map."Function"
FROM 
    system."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_Region_Mapping" AS region_map
ON 
    fact."Company_Code"::text = region_map."CoCd"::text
LEFT JOIN 
    dim."IMETA_Entity_Mapping" AS entity_map
ON 
    region_map."BRACS Entity" = entity_map."Entity"
LEFT JOIN 
    dim."IMETA_Source_Description_Mapping" AS src_desc_map
ON 
    ltrim(fact."BRACS_FA", '0') = src_desc_map."BRACSFA"
WHERE 
    src_desc_map."BRACSFA" IS NOT NULL
LIMIT 50;

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 9

The temp table needs to have all the columns in the select.

After the first insert into the temp table you just insert rows that don't already exist in the temp table from the other queries.

yrstruly Hall of Fame Points: 3475 More actions · Answer 10

My Temp table:

-- CTE to handle duplicates
WITH deduplicated AS (
    SELECT DISTINCT ON ("ZTBR_TransactionCode")
        fact."ZTBR_TransactionCode",
        fact."Company_Code",
        fact."Posting_Period",
        fact."Fiscal_Year",
        fact."Profit_Center",
        fact."Account_Number",
        fact."Business_Process",
        fact."Internal_Order",
        fact."Amount_in_Company_Code_Currency",
        fact."Company_Code_Currency",
        fact."BRACS_FA",
        fact."Expense_Type",
        fact."BRACS_ACCT_Key",
        fact."CC_Direct",
        fact."Segment_PC",
        fact."CC_Master_FA",
        fact."Region_Secondary_Key",
        fact."Direct_Indirect_Secondary_Key",
        fact."Source_Description_Secondary_Key",
        fact."Entity_Secondary_Key",
        fact."Master_BRACS_Secondary_Key",
        src_desc_map."BRACSFA",
        src_desc_map."Function"
    FROM 
        system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
    -- Joins and conditions
    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_Region_Mapping" AS region_map
        ON fact."Company_Code"::text = region_map."CoCd"::text
    LEFT JOIN dim."IMETA_Entity_Mapping" AS entity_map
        ON region_map."BRACS Entity" = entity_map."Entity"
    LEFT JOIN dim."IMETA_Source_Description_Mapping" AS src_desc_map
        ON ltrim(fact."BRACS_FA", '0') = src_desc_map."BRACSFA"
    WHERE 
        src_desc_map."BRACSFA" IS NOT NULL
)
-- Actual Insert statement
INSERT INTO temp_table_name (
    "ZTBR_TransactionCode",
    "Company_Code",
    "Posting_Period",
    "Fiscal_Year",
    "Profit_Center",
    "Account_Number",
    "Business_Process",
    "Internal_Order",
    "Amount_in_Company_Code_Currency",
    "Company_Code_Currency",
    "BRACS_FA",
    "Expense_Type",
    "BRACS_ACCT_Key",
    "CC_Direct",
    "Segment_PC",
    "CC_Master_FA",
    "Region_Secondary_Key",
    "Direct_Indirect_Secondary_Key",
    "Source_Description_Secondary_Key",
    "Entity_Secondary_Key",
    "Master_BRACS_Secondary_Key",
    "BRACSFA",
    "Function"
)
SELECT * FROM deduplicated
ON CONFLICT ("ZTBR_TransactionCode") 
DO UPDATE SET 
    "Company_Code" = EXCLUDED."Company_Code",
    "Posting_Period" = EXCLUDED."Posting_Period",
    "Fiscal_Year" = EXCLUDED."Fiscal_Year",
    "Profit_Center" = EXCLUDED."Profit_Center",
    "Account_Number" = EXCLUDED."Account_Number",
    "Business_Process" = EXCLUDED."Business_Process",
    "Internal_Order" = EXCLUDED."Internal_Order",
    "Amount_in_Company_Code_Currency" = EXCLUDED."Amount_in_Company_Code_Currency",
    "Company_Code_Currency" = EXCLUDED."Company_Code_Currency",
    "BRACS_FA" = EXCLUDED."BRACS_FA",
    "Expense_Type" = EXCLUDED."Expense_Type",
    "BRACS_ACCT_Key" = EXCLUDED."BRACS_ACCT_Key",
    "CC_Direct" = EXCLUDED."CC_Direct",
    "Segment_PC" = EXCLUDED."Segment_PC",
    "CC_Master_FA" = EXCLUDED."CC_Master_FA",
    "Region_Secondary_Key" = EXCLUDED."Region_Secondary_Key",
    "Direct_Indirect_Secondary_Key" = EXCLUDED."Direct_Indirect_Secondary_Key",
    "Source_Description_Secondary_Key" = EXCLUDED."Source_Description_Secondary_Key",
    "Entity_Secondary_Key" = EXCLUDED."Entity_Secondary_Key",
    "Master_BRACS_Secondary_Key" = EXCLUDED."Master_BRACS_Secondary_Key",
    "BRACSFA" = EXCLUDED."BRACSFA",
    "Function" = EXCLUDED."Function";

For my final table i would like an inner join consisting of the columns/tables of my Temp table. You mentioned i can do an inner join to avoid nulls?

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 11

yrstruly wrote:

For my final table i would like an inner join consisting of the columns/tables of my Temp table. You mentioned i can do an inner join to avoid nulls?

If you left join to a table then if there is no matching row then you will get nulls. If you inner join then you will only get rows that match. If any of the columns in the table are null then obviously you will get null back even on an inner join as that is the value stored in the column.

yrstruly Hall of Fame Points: 3475 More actions · Answer 12

Thank you. Will you have a look at this , https://www.sqlservercentral.com/forums/topic/calculating-days-timeare-loops-neccessary, please

yrstruly Hall of Fame Points: 3475 More actions · Answer 13

Only getting data for one of the dimension tables in Test table: