Thread: Unique Primary Key Linked to Multiple Accounts

Unique Primary Key Linked to Multiple Accounts

From

Anthony Apollis

Date:

13 November 2023, 10:02:41

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.

Re: Unique Primary Key Linked to Multiple Accounts

From

"David G. Johnston"

Date:

13 November 2023, 10:22:54

On Monday, November 13, 2023, Anthony Apollis <anthony.apollis@gmail.com> wrote:

Please advice.

The subset of data you’ve chosen to show does not allow one to draw any conclusions. Look for the things that are different in the joined result - the things that are identical are mostly uninteresting when trying to figure out what additional join conditions are needed so that only a single matching row is found.

Your other table doesn’t have a primary key defined. If you do that the answer will likely present itself to you.

David J.

Re: Unique Primary Key Linked to Multiple Accounts

From

Adrian Klaver

Date:

13 November 2023, 19:45:24

On 11/12/23 23:02, Anthony Apollis wrote:
> Please advice. I brought in data from SAP and assigned unique primary 
> key to the table:
> 

> 
> I joined it with a dimension table.
> 
> Joining code
> 
> fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW"ASfact
> LEFTJOINdim."IMETA_BRACS_Mapping"ASbracs_map
> ONfact."Account_Number"=bracs_map."GCoA"ANDfact."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.

This is not a problem it is the nature of the table definitions and the 
query. The PK is "ZTBR_TransactionCode", but you are joining on 
fact."Account_Number"=bracs_map."GCoA"ANDfact."Expense_Type"=bracs_map."EXPENSE 
FLAG". Since you indicate that there are multiple account numbers in the 
table then it is no surprise that the "ZTBR_TransactionCode" is repeated.

> Please advice.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Unique Primary Key Linked to Multiple Accounts

From

Adrian Klaver

Date:

13 November 2023, 19:47:03

On 11/13/23 08:45, Adrian Klaver wrote:
> On 11/12/23 23:02, Anthony Apollis wrote:
>> Please advice. I brought in data from SAP and assigned unique primary 
>> key to the table:
>>
> 
>>
>> I joined it with a dimension table.
>>
>> Joining code
>>
>> fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW"ASfact
>> LEFTJOINdim."IMETA_BRACS_Mapping"ASbracs_map
>> ONfact."Account_Number"=bracs_map."GCoA"ANDfact."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.
> 
> This is not a problem it is the nature of the table definitions and the 
> query. The PK is "ZTBR_TransactionCode", but you are joining on 
> fact."Account_Number"=bracs_map."GCoA"ANDfact."Expense_Type"=bracs_map."EXPENSE FLAG". Since you indicate that there
aremultiple account numbers in the table then it is no surprise that the "ZTBR_TransactionCode" is repeated.
 

Aah, that should be '... multiple repeated account numbers in the table ...'
> 
> 
>> Please advice.
>>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Unique Primary Key Linked to Multiple Accounts

From

Adrian Klaver

Date:

13 November 2023, 21:05:44

On 11/13/23 09:54, Anthony Apollis wrote:

Please reply to list also
Ccing list

> Hi Adrian
> 
> Yes, the Account number column(s) are not unique. I brought in the 
> primary keys in both tables. If I enforce referential integrity on the 
> dimension table, will this solve the issue?

1) Your original post shows no PK for dim."IMETA_BRACS_Mapping_".

2) Define '...enforce referential integrity on the dimension table, 
...'. In other words provide the Foreign Key relationship you plan to 
set up. Though I doubt that will solve anything, because I don't 
actually see an issue.

> I am struggling to select distinct values from my tables, I'm working 
> via remote server and get connection lost issues.
> When i use "ORDER BY "Source data.Company Code"" i get distinct rows:
> o.png

3) Do not use images, copy and paste as text. Are they distinct all the 
way through or just for a given  "Source data.Company Code"?

> 
> I plan to extract distinct columns/rows from my destination table and 
> write a view that will access this unique data. I am even 
> struggling with this code below, it loses connection. Apparently it uses 
> too much memory for 5 million + records. Any suggestions?

4) Have no idea what the below has to do with creating a view?

> 
> DO $$
> DECLARE
>    row_count INTEGER := 100;
>    offset_val INTEGER := 0;
>    inserted_rows INTEGER;
> BEGIN
>    LOOP
>      INSERT INTO model.staging_ZTRB_BRACS_Combined (
>        "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",
>        "Acct Type",
>        "Level 1",
>        "Level 2",
>        "Level 3",
>        "GCoA",
>        "Account Desc",
>        "EXPENSE FLAG",
>        "BRACS",
>        "BRACS_DESC",
>        "Source data.Company Code",
>        "Source data.Currency",
>        "Source data.Account",
>        "Source data.Account Description",
>        "Source data.BRACS Account",
>        "Source data.BRACS Account Description",
>        "Source data.IS/BS",
>        "Source data.Classification",
>        "Source data.Function",
>        "Source data.Region",
>        "Source data.Roll - Up"
>      )
>      SELECT
>        DISTINCT 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",
>        bracs."Acct Type",
>        bracs."Level 1",
>        bracs."Level 2",
>        bracs."Level 3",
>        bracs."GCoA",
>        bracs."Account Desc",
>        bracs."EXPENSE FLAG",
>        bracs."BRACS",
>        bracs."BRACS_DESC",
>        bracs."Source data.Company Code",
>        bracs."Source data.Currency",
>        bracs."Source data.Account",
>        bracs."Source data.Account Description",
>        bracs."Source data.BRACS Account",
>        bracs."Source data.BRACS Account Description",
>        bracs."Source data.IS/BS",
>        bracs."Source data.Classification",
>        bracs."Source data.Function",
>        bracs."Source data.Region",
>        bracs."Source data.Roll - Up"
>      FROM
>        fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
>      LEFT JOIN
>        dim."IMETA_BRACS_Mapping_" AS bracs
>         ON
>        fact."Account_Number" = bracs."GCoA" AND
>        fact."Expense_Type" = bracs."EXPENSE FLAG"
>      LIMIT row_count OFFSET offset_val;
> 
>      GET DIAGNOSTICS inserted_rows = ROW_COUNT;
> 
>      -- Exit when the number of inserted rows is less than row_count
>      IF inserted_rows < row_count THEN
>        EXIT;
>      END IF;
> 
>      offset_val := offset_val + row_count;
>    END LOOP;
> END $$;
> 
> 
> On Mon, 13 Nov 2023 at 18:47, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 11/13/23 08:45, Adrian Klaver wrote:
>      > On 11/12/23 23:02, Anthony Apollis wrote:
>      >> Please advice. I brought in data from SAP and assigned unique
>     primary
>      >> key to the table:
>      >>
>      >
>      >>
>      >> I joined it with a dimension table.
>      >>
>      >> Joining code
>      >>
>      >> fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW"ASfact
>      >> LEFTJOINdim."IMETA_BRACS_Mapping"ASbracs_map
>      >>
>     ONfact."Account_Number"=bracs_map."GCoA"ANDfact."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.
>      >
>      > This is not a problem it is the nature of the table definitions
>     and the
>      > query. The PK is "ZTBR_TransactionCode", but you are joining on
>      >
>     fact."Account_Number"=bracs_map."GCoA"ANDfact."Expense_Type"=bracs_map."EXPENSE FLAG". Since you indicate that
thereare multiple account numbers in the table then it is no surprise that the "ZTBR_TransactionCode" is repeated.
 
> 
>     Aah, that should be '... multiple repeated account numbers in the
>     table ...'
>      >
>      >
>      >> Please advice.
>      >>
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Unique Primary Key Linked to Multiple Accounts

From

Anthony Apollis

Date:

13 November 2023, 21:14:46

I cant get distinct data, im tying to break up the insert into chunks and it does not help

On Mon, 13 Nov 2023 at 20:05, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 11/13/23 09:54, Anthony Apollis wrote:

Please reply to list also
Ccing list

> Hi Adrian
>
> Yes, the Account number column(s) are not unique. I brought in the
> primary keys in both tables. If I enforce referential integrity on the
> dimension table, will this solve the issue?

1) Your original post shows no PK for dim."IMETA_BRACS_Mapping_".

2) Define '...enforce referential integrity on the dimension table,
...'. In other words provide the Foreign Key relationship you plan to
set up. Though I doubt that will solve anything, because I don't
actually see an issue.

> I am struggling to select distinct values from my tables, I'm working
> via remote server and get connection lost issues.
> When i use "ORDER BY "Source data.Company Code"" i get distinct rows:
> o.png

3) Do not use images, copy and paste as text. Are they distinct all the
way through or just for a given "Source data.Company Code"?

>
> I plan to extract distinct columns/rows from my destination table and
> write a view that will access this unique data. I am even
> struggling with this code below, it loses connection. Apparently it uses
> too much memory for 5 million + records. Any suggestions?

4) Have no idea what the below has to do with creating a view?

>
> DO $$
> DECLARE
> row_count INTEGER := 100;
> offset_val INTEGER := 0;
> inserted_rows INTEGER;
> BEGIN
> LOOP
> INSERT INTO model.staging_ZTRB_BRACS_Combined (
> "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",
> "Acct Type",
> "Level 1",
> "Level 2",
> "Level 3",
> "GCoA",
> "Account Desc",
> "EXPENSE FLAG",
> "BRACS",
> "BRACS_DESC",
> "Source data.Company Code",
> "Source data.Currency",
> "Source data.Account",
> "Source data.Account Description",
> "Source data.BRACS Account",
> "Source data.BRACS Account Description",
> "Source data.IS/BS",
> "Source data.Classification",
> "Source data.Function",
> "Source data.Region",
> "Source data.Roll - Up"
> )
> SELECT
> DISTINCT 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",
> bracs."Acct Type",
> bracs."Level 1",
> bracs."Level 2",
> bracs."Level 3",
> bracs."GCoA",
> bracs."Account Desc",
> bracs."EXPENSE FLAG",
> bracs."BRACS",
> bracs."BRACS_DESC",
> bracs."Source data.Company Code",
> bracs."Source data.Currency",
> bracs."Source data.Account",
> bracs."Source data.Account Description",
> bracs."Source data.BRACS Account",
> bracs."Source data.BRACS Account Description",
> bracs."Source data.IS/BS",
> bracs."Source data.Classification",
> bracs."Source data.Function",
> bracs."Source data.Region",
> bracs."Source data.Roll - Up"
> FROM
> fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
> LEFT JOIN
> dim."IMETA_BRACS_Mapping_" AS bracs
> ON
> fact."Account_Number" = bracs."GCoA" AND
> fact."Expense_Type" = bracs."EXPENSE FLAG"
> LIMIT row_count OFFSET offset_val;
>
> GET DIAGNOSTICS inserted_rows = ROW_COUNT;
>
> -- Exit when the number of inserted rows is less than row_count
> IF inserted_rows < row_count THEN
> EXIT;
> END IF;
>
> offset_val := offset_val + row_count;
> END LOOP;
> END $$;
>
>
> On Mon, 13 Nov 2023 at 18:47, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 11/13/23 08:45, Adrian Klaver wrote:
> > On 11/12/23 23:02, Anthony Apollis wrote:
> >> Please advice. I brought in data from SAP and assigned unique
> primary
> >> key to the table:
> >>
> >
> >>
> >> I joined it with a dimension table.
> >>
> >> Joining code
> >>
> >> fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW"ASfact
> >> LEFTJOINdim."IMETA_BRACS_Mapping"ASbracs_map
> >>
> ONfact."Account_Number"=bracs_map."GCoA"ANDfact."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.
> >
> > This is not a problem it is the nature of the table definitions
> and the
> > query. The PK is "ZTBR_TransactionCode", but you are joining on
> >
> fact."Account_Number"=bracs_map."GCoA"ANDfact."Expense_Type"=bracs_map."EXPENSE FLAG". Since you indicate that there are multiple account numbers in the table then it is no surprise that the "ZTBR_TransactionCode" is repeated.
>
> Aah, that should be '... multiple repeated account numbers in the
> table ...'
> >
> >
> >> Please advice.
> >>
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Unique Primary Key Linked to Multiple Accounts

From

Adrian Klaver

Date:

13 November 2023, 21:19:00

On 11/13/23 10:14, Anthony Apollis wrote:
> I cant get distinct data, im tying to break up the insert into chunks 
> and it does not help

Statements without context will not yield answers. First and foremost 
you will need to define what you mean by distinct data? From the queries 
you show that is not happening.

-- 
Adrian Klaver
adrian.klaver@aklaver.com