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@xxxxxxxxxxx> 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@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
