Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

Oracle Merge Using Virtual Column
-- 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')))
);

Oracle 11g Virtual Column Creation

-- 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;

Oracle 11g Merge With Virtual Column


Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: October 07, 2014

  39911