Unique Primary Key Linked to Multiple Accounts

Поиск
Список
Период
Сортировка
От Anthony Apollis
Тема Unique Primary Key Linked to Multiple Accounts
Дата
Msg-id CAJyMCYK2rpd2AH+V6reQRtfprLCEzLzQsPBdOrC8RXVrzLH3wg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Unique Primary Key Linked to Multiple Accounts
Re: Unique Primary Key Linked to Multiple Accounts
Список pgsql-general

Please advice. I brought in data from SAP and assigned unique primary key to the table:

unique

CREATE TABLE IF NOT EXISTS fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"ZTBR_TransactionCode" integer NOT NULL,
"Company_Code" character varying COLLATE pg_catalog."default",
"Posting_Period" integer,
"Fiscal_Year" integer,
"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,
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey" PRIMARY KEY ("ZTBR_TransactionCode")
)

TABLESPACE pg_default;

I joined it with a dimension table.

Joining code

 fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
LEFT JOIN dim."IMETA_BRACS_Mapping" AS bracs_map
ON fact."Account_Number" = bracs_map."GCoA" AND fact."Expense_Type" = bracs_map."EXPENSE FLAG"

It is joined on the Account numbers, which appears in the table multiple times. Problem is the Unique Primary Key is then mapped to these Account numbers multiple times.

unique2

CREATE TABLE IF NOT EXISTS dim."IMETA_BRACS_Mapping_"
(
"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" character varying(255) COLLATE pg_catalog."default",
"EXPENSE FLAG" character varying(255) COLLATE pg_catalog."default",
"BRACS" integer,
"BRACS_DESC" character varying(255) COLLATE pg_catalog."default",
"Source data.Company Code" character varying(255) COLLATE pg_catalog."default",
"Source data.Currency" character varying(255) COLLATE pg_catalog."default",
"Source data.Account" integer,
"Source data.Account Description" character varying(255) COLLATE pg_catalog."default",
"Source data.BRACS Account" integer,
"Source data.BRACS Account Description" character varying(255) COLLATE pg_catalog."default",
"Source data.IS/BS" character varying(255) COLLATE pg_catalog."default",
"Source data.Classification" character varying(255) COLLATE pg_catalog."default",
"Source data.Function" character varying(255) COLLATE pg_catalog."default",
"Source data.Region" character varying(255) COLLATE pg_catalog."default",
"Source data.Roll - Up" character varying(255) COLLATE pg_catalog."default"
)

TABLESPACE pg_default;

Result:

unique3

Please advice.

В списке pgsql-general по дате отправления:

Предыдущее
От: Shaik Mohammad Mujeeb
Дата:
Сообщение: Issue in compiling postgres on latest macOS 14.1.1
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Unique Primary Key Linked to Multiple Accounts