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
