Re: Unique Primary Key Linked to Multiple Accounts - Mailing list pgsql-general

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