Unique Primary Key Linked to Multiple Accounts

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.

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

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

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

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