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.kla...@aklaver.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.kla...@aklaver.com > > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
