-- Create a virtual column invoice_date_id derived from invoice_date ALTER TABLE edw_sales_fact ADD (invoice_date_id AS (TO_NUMBER(TO_CHAR(invoice_date,'yyyymmdd'))) ); -- Merge data into fact table with a join on a virtual column -- In data merge situation, it is convenient to join on a derived identifier and -- load data without functions (TRUNC(date_col) or TO_CHAR(date_col,'yyyymmdd')) -- in the join by using virtual column(s). MERGE INTO edw_sales_fact m USING (SELECT 100 div_id, 9003 inv_id, 195 qty, 20105 sales_amt, '18-MAR-2011' inv_date, 20110318 inv_ndate FROM DUAL) s ON (m.division_id = s.div_id AND m.invoice_id = s.inv_id AND m.invoice_date_id = s.inv_ndate) WHEN MATCHED THEN UPDATE SET m.quantity = s.qty, m.sales_amt = s.sales_amt WHEN NOT MATCHED THEN INSERT (division_id, invoice_id, quantity, sales_amt, invoice_date) VALUES (s.div_id, s.inv_id, s.qty, s.sales_amt, s.inv_date); COMMIT;
24083