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:
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.
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:
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