Re: Unique Primary Key Linked to Multiple Accounts

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>