Re: Unique Primary Key Linked to Multiple Accounts

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