asked on
MERGE
INTO
DW_REWARD_MNTHLY_AGG DW_REWARD_MNTHLY_AGG
USING
(SELECT
TO_NUMBER ( DW_REWARD_MNTHLY_AGG_VW.CALENDAR_MONTH_DW_ID )/* EXPRESSION.OUTGRP1.CALENDAR_MONTH_DW_ID */ CALENDAR_MONTH_DW_ID,
DW_REWARD_MNTHLY_AGG_VW.INVITED_USER_CNT INVITED_USER_CNT,
DW_REWARD_MNTHLY_AGG_VW.ENROLLED_USER_CNT ENROLLED_USER_CNT,
DW_REWARD_MNTHLY_AGG_VW.TRANS_CNRM_MTH_CNT TRANS_CNRM_MTH_CNT,
DW_REWARD_MNTHLY_AGG_VW.TRANS_CNCL_MTH_CNT TRANS_CNCL_MTH_CNT,
DW_REWARD_MNTHLY_AGG_VW.TKT_SALE_CNRM_MTH_AMT TKT_SALE_CNRM_MTH_AMT,
DW_REWARD_MNTHLY_AGG_VW.TKT_SALE_CNCL_MTH_AMT TKT_SALE_CNCL_MTH_AMT,
DW_REWARD_MNTHLY_AGG_VW.TKT_CNRM_MTH_CNT TKT_CNRM_MTH_CNT,
DW_REWARD_MNTHLY_AGG_VW.TKT_CNCL_MTH_CNT TKT_CNCL_MTH_CNT,
DW_REWARD_MNTHLY_AGG_VW.TOT_COST_CNRM_MTH_AMT TOT_COST_CNRM_MTH_AMT,
DW_REWARD_MNTHLY_AGG_VW.TOT_COST_CNCL_MTH_AMT TOT_COST_CNCL_MTH_AMT,
DW_REWARD_MNTHLY_AGG_VW.ISSUED_DISCNT_ISSUED_MTH_AMT ISSUED_DISCNT_ISSUED_MTH_AMT,
DW_REWARD_MNTHLY_AGG_VW.REEDEMED_DISCNT_ISSUED_MTH_AMT REEDEMED_DISCNT_ISSUED_MTH_AMT,
DW_REWARD_MNTHLY_AGG_VW.DW_CREATED_DTTM DW_CREATED_DTTM,
DW_REWARD_MNTHLY_AGG_VW.DW_LASTUPDATE_DTTM DW_LASTUPDATE_DTTM
FROM
DW_REWARD_MNTHLY_AGG_VW DW_REWARD_MNTHLY_AGG_VW
)
MERGE_SUBQUERY
ON (
DW_REWARD_MNTHLY_AGG.CALENDAR_MONTH_DW_ID = MERGE_SUBQUERY.CALENDAR_MONTH_DW_ID
)
WHEN NOT MATCHED THEN
INSERT
(DW_REWARD_MNTHLY_AGG.CALENDAR_MONTH_DW_ID,
DW_REWARD_MNTHLY_AGG.INVITED_USER_CNT,
DW_REWARD_MNTHLY_AGG.ENROLLED_USER_CNT,
DW_REWARD_MNTHLY_AGG.TRANS_CNRM_MTH_CNT,
DW_REWARD_MNTHLY_AGG.TRANS_CNCL_MTH_CNT,
DW_REWARD_MNTHLY_AGG.TKT_SALE_CNRM_MTH_AMT,
DW_REWARD_MNTHLY_AGG.TKT_SALE_CNCL_MTH_AMT,
DW_REWARD_MNTHLY_AGG.TKT_CNRM_MTH_CNT,
DW_REWARD_MNTHLY_AGG.TKT_CNCL_MTH_CNT,
DW_REWARD_MNTHLY_AGG.TOT_COST_CNRM_MTH_AMT,
DW_REWARD_MNTHLY_AGG.TOT_COST_CNCL_MTH_AMT,
DW_REWARD_MNTHLY_AGG.ISSUED_DISCNT_ISSUED_MTH_AMT,
DW_REWARD_MNTHLY_AGG.REEDEMED_DISCNT_ISSUED_MTH_AMT,
DW_REWARD_MNTHLY_AGG.DW_CREATED_DTTM,
DW_REWARD_MNTHLY_AGG.DW_LASTUPDATE_DTTM)
VALUES
(MERGE_SUBQUERY.CALENDAR_MONTH_DW_ID,
MERGE_SUBQUERY.INVITED_USER_CNT,
MERGE_SUBQUERY.ENROLLED_USER_CNT,
MERGE_SUBQUERY.TRANS_CNRM_MTH_CNT,
MERGE_SUBQUERY.TRANS_CNCL_MTH_CNT,
MERGE_SUBQUERY.TKT_SALE_CNRM_MTH_AMT,
MERGE_SUBQUERY.TKT_SALE_CNCL_MTH_AMT,
MERGE_SUBQUERY.TKT_CNRM_MTH_CNT,
MERGE_SUBQUERY.TKT_CNCL_MTH_CNT,
MERGE_SUBQUERY.TOT_COST_CNRM_MTH_AMT,
MERGE_SUBQUERY.TOT_COST_CNCL_MTH_AMT,
MERGE_SUBQUERY.ISSUED_DISCNT_ISSUED_MTH_AMT,
MERGE_SUBQUERY.REEDEMED_DISCNT_ISSUED_MTH_AMT,
MERGE_SUBQUERY.DW_CREATED_DTTM,
MERGE_SUBQUERY.DW_LASTUPDATE_DTTM)
WHEN MATCHED THEN
UPDATE
SET
INVITED_USER_CNT = MERGE_SUBQUERY.INVITED_USER_CNT,
ENROLLED_USER_CNT = MERGE_SUBQUERY.ENROLLED_USER_CNT,
TRANS_CNRM_MTH_CNT = MERGE_SUBQUERY.TRANS_CNRM_MTH_CNT,
TRANS_CNCL_MTH_CNT = MERGE_SUBQUERY.TRANS_CNCL_MTH_CNT,
TKT_SALE_CNRM_MTH_AMT = MERGE_SUBQUERY.TKT_SALE_CNRM_MTH_AMT,
TKT_SALE_CNCL_MTH_AMT = MERGE_SUBQUERY.TKT_SALE_CNCL_MTH_AMT,
TKT_CNRM_MTH_CNT = MERGE_SUBQUERY.TKT_CNRM_MTH_CNT,
TKT_CNCL_MTH_CNT = MERGE_SUBQUERY.TKT_CNCL_MTH_CNT,
TOT_COST_CNRM_MTH_AMT = MERGE_SUBQUERY.TOT_COST_CNRM_MTH_AMT,
TOT_COST_CNCL_MTH_AMT = MERGE_SUBQUERY.TOT_COST_CNCL_MTH_AMT,
ISSUED_DISCNT_ISSUED_MTH_AMT = MERGE_SUBQUERY.ISSUED_DISCNT_ISSUED_MTH_AMT,
REEDEMED_DISCNT_ISSUED_MTH_AMT = MERGE_SUBQUERY.REEDEMED_DISCNT_ISSUED_MTH_AMT,
DW_CREATED_DTTM = MERGE_SUBQUERY.DW_CREATED_DTTM,
DW_LASTUPDATE_DTTM = MERGE_SUBQUERY.DW_LASTUPDATE_DTTM;
ASKER
create or replace force test_Vw as
with
a as
(select id,nm from te),
b as
(select nm, addr from te_lkp)
select id, nvl(a.nm,b.nm) as nm, addr
from a,b
where a.nm=b.nm(+)
union
select id, nvl(a.nm,b.nm) as nm, addr
from a,b
where a.nm(+)=b.nm
ASKER
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
TRUSTED BY
ASKER
Open in new window