视图与物理视图详细例子
DROP materialized view ATOMBB.MV_GO_SHA_DANSU_RPT;
create materialized view ATOMBB.MV_GO_SHA_DANSU_RPT TABLESPACE USR_FEE_DATA16K_01 REFRESH FORCE ON DEMAND AS
SELECT
SUM(decode(tafd.ksai_sha_code, 1, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_T,
SUM(decode(tafd.ksai_sha_code, 2, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_O,
SUM(decode(tafd.ksai_sha_code, 3, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_S,
SUM(decode(tafd.ksai_sha_code, 4, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_N,
SUM(decode(tafd.ksai_sha_code, 5, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_H,
SUM(decode(tafd.ksai_sha_code, 1, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_T,
SUM(decode(tafd.ksai_sha_code, 2, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_O,
SUM(decode(tafd.ksai_sha_code, 3, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_S,
SUM(decode(tafd.ksai_sha_code, 4, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_N,
SUM(decode(tafd.ksai_sha_code, 5, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_H,
0 AS JITSU_DANSU_SUM_T,
0 AS JITSU_DANSU_SUM_O,
0 AS JITSU_DANSU_SUM_S,
0 AS JITSU_DANSU_SUM_N,
0 AS JITSU_DANSU_SUM_H,
to_char(tafd.ksai_date,'yyyyMM') AS KSAI_DATE_YYYYMM,
tafd.moko_kind,
tafd.uriage_nengetsu,
tafd.Uriage_Sha_Code,
tafd.Ksai_Sha_Code,
tafd.tanto_bu_ka_code,
tafd.gyosyu_mcode,
tafd.adv_baitai_bunrui_code,
tafd.uriage_shime_status,
tafd.del_flg,
tafd.karihan_sign,
tafd.jisseki_sign
FROM tb_adv_fee_detail tafd,
TB_TOKEI_DANSU_KANRI ttdk
WHERE tafd.fee_mgmt_num = ttdk.fee_mgmt_num(+) AND tafd.fee_mgmt_eda = ttdk.fee_mgmt_eda(+)
AND to_char(tafd.ksai_date,'yyyyMM') <=TO_CHAR(add_months(SYSDATE,-3),'YYYYMM')
AND tafd.Moko_Kind NOT IN ('21','22')
AND tafd.uriage_shime_status = '1'
AND tafd.jisseki_sign = '1'
AND tafd.del_flg = '0'
AND ttdk.del_flg(+) = '0'
GROUP BY
to_char(tafd.ksai_date,'yyyyMM'),
tafd.Uriage_Sha_Code,
tafd.uriage_nengetsu,
tafd.Ksai_Sha_Code,
tafd.tanto_bu_ka_code,
tafd.gyosyu_mcode,
tafd.adv_baitai_bunrui_code,
tafd.uriage_shime_status,
tafd.del_flg,
tafd.karihan_sign,
tafd.jisseki_sign,
tafd.moko_kind
UNION ALL
SELECT
0 AS TOKEI_DANSU_SUM_T,
0 AS TOKEI_DANSU_SUM_O,
0 AS TOKEI_DANSU_SUM_S,
0 AS TOKEI_DANSU_SUM_N,
0 AS TOKEI_DANSU_SUM_H,
0 AS MOKO_DANSU_SUM_T,
0 AS MOKO_DANSU_SUM_O,
0 AS MOKO_DANSU_SUM_S,
0 AS MOKO_DANSU_SUM_N,
0 AS MOKO_DANSU_SUM_H,
SUM(decode(tafd.ksai_sha_code, 1, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_T,
SUM(decode(tafd.ksai_sha_code, 2, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_O,
SUM(decode(tafd.ksai_sha_code, 3, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_S,
SUM(decode(tafd.ksai_sha_code, 4, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_N,
SUM(decode(tafd.ksai_sha_code, 5, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_H,
to_char(tafd.ksai_date,'yyyyMM') AS KSAI_DATE_YYYYMM,
tafd.moko_kind,
tafd.uriage_nengetsu,
tafd.Uriage_Sha_Code,
tafd.Ksai_Sha_Code,
tafd.tanto_bu_ka_code,
tafd.gyosyu_mcode,
tafd.adv_baitai_bunrui_code,
tafd.uriage_shime_status,
tafd.del_flg,
tafd.karihan_sign,
tafd.jisseki_sign
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY T.annai_fee_mgmt_num order by T.FEE_KAKUTEI_DATE_TIME desc) rn, T.*
FROM tb_adv_fee_detail T
WHERE to_char(T.ksai_date,'yyyyMM') <=TO_CHAR(add_months(SYSDATE,-3),'YYYYMM')
AND T.Moko_Kind IN ('21','22')
AND T.uriage_shime_status = '1'
--AND T.jisseki_sign = '1'
AND T.del_flg = '0'
)TAFD
WHERE TAFD.RN=1
AND TAFD.jisseki_sign = '1'
GROUP BY
to_char(tafd.ksai_date,'yyyyMM'),
tafd.Uriage_Sha_Code,
tafd.uriage_nengetsu,
tafd.Ksai_Sha_Code,
tafd.tanto_bu_ka_code,
tafd.gyosyu_mcode,
tafd.adv_baitai_bunrui_code,
tafd.uriage_shime_status,
tafd.del_flg,
tafd.karihan_sign,
tafd.jisseki_sign,
tafd.moko_kind;
————————————————————————————————————————————————————————
create OR REPLACE view ATOMBB.V_GO_SHA_DANSU_RPT AS
SELECT * FROM MV_GO_SHA_DANSU_RPT
UNION ALL
SELECT
SUM(decode(tafd.ksai_sha_code, 1, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_T,
SUM(decode(tafd.ksai_sha_code, 2, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_O,
SUM(decode(tafd.ksai_sha_code, 3, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_S,
SUM(decode(tafd.ksai_sha_code, 4, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_N,
SUM(decode(tafd.ksai_sha_code, 5, ttdk.tokei_dansu, 0)) AS TOKEI_DANSU_SUM_H,
SUM(decode(tafd.ksai_sha_code, 1, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_T,
SUM(decode(tafd.ksai_sha_code, 2, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_O,
SUM(decode(tafd.ksai_sha_code, 3, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_S,
SUM(decode(tafd.ksai_sha_code, 4, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_N,
SUM(decode(tafd.ksai_sha_code, 5, tafd.moko_dansu, 0)) AS MOKO_DANSU_SUM_H,
0 AS JITSU_DANSU_SUM_T,
0 AS JITSU_DANSU_SUM_O,
0 AS JITSU_DANSU_SUM_S,
0 AS JITSU_DANSU_SUM_N,
0 AS JITSU_DANSU_SUM_H,
to_char(tafd.ksai_date,'yyyyMM') AS KSAI_DATE_YYYYMM,
tafd.moko_kind,
tafd.uriage_nengetsu,
tafd.Uriage_Sha_Code,
tafd.Ksai_Sha_Code,
tafd.tanto_bu_ka_code,
tafd.gyosyu_mcode,
tafd.adv_baitai_bunrui_code,
tafd.uriage_shime_status,
tafd.del_flg,
tafd.karihan_sign,
tafd.jisseki_sign
FROM tb_adv_fee_detail tafd,
TB_TOKEI_DANSU_KANRI ttdk
WHERE tafd.fee_mgmt_num = ttdk.fee_mgmt_num(+) AND tafd.fee_mgmt_eda = ttdk.fee_mgmt_eda(+)
AND to_char(tafd.ksai_date,'yyyyMM') >TO_CHAR(add_months(SYSDATE,-3),'YYYYMM')
AND tafd.Moko_Kind NOT IN ('21','22')
AND tafd.uriage_shime_status = '1'
AND tafd.jisseki_sign = '1'
AND tafd.del_flg = '0'
AND ttdk.del_flg(+) = '0'
GROUP BY
to_char(tafd.ksai_date,'yyyyMM'),
tafd.Uriage_Sha_Code,
tafd.uriage_nengetsu,
tafd.Ksai_Sha_Code,
tafd.tanto_bu_ka_code,
tafd.gyosyu_mcode,
tafd.adv_baitai_bunrui_code,
tafd.uriage_shime_status,
tafd.del_flg,
tafd.karihan_sign,
tafd.jisseki_sign,
tafd.moko_kind
UNION ALL
SELECT
0 AS TOKEI_DANSU_SUM_T,
0 AS TOKEI_DANSU_SUM_O,
0 AS TOKEI_DANSU_SUM_S,
0 AS TOKEI_DANSU_SUM_N,
0 AS TOKEI_DANSU_SUM_H,
0 AS MOKO_DANSU_SUM_T,
0 AS MOKO_DANSU_SUM_O,
0 AS MOKO_DANSU_SUM_S,
0 AS MOKO_DANSU_SUM_N,
0 AS MOKO_DANSU_SUM_H,
SUM(decode(tafd.ksai_sha_code, 1, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_T,
SUM(decode(tafd.ksai_sha_code, 2, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_O,
SUM(decode(tafd.ksai_sha_code, 3, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_S,
SUM(decode(tafd.ksai_sha_code, 4, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_N,
SUM(decode(tafd.ksai_sha_code, 5, tafd.jitsu_dansu*10, 0)) AS JITSU_DANSU_SUM_H,
to_char(tafd.ksai_date,'yyyyMM') AS KSAI_DATE_YYYYMM,
tafd.moko_kind,
tafd.uriage_nengetsu,
tafd.Uriage_Sha_Code,
tafd.Ksai_Sha_Code,
tafd.tanto_bu_ka_code,
tafd.gyosyu_mcode,
tafd.adv_baitai_bunrui_code,
tafd.uriage_shime_status,
tafd.del_flg,
tafd.karihan_sign,
tafd.jisseki_sign
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY T.annai_fee_mgmt_num order by T.FEE_KAKUTEI_DATE_TIME desc) rn, T.*
FROM tb_adv_fee_detail T
WHERE to_char(T.ksai_date,'yyyyMM') >TO_CHAR(add_months(SYSDATE,-3),'YYYYMM')
AND T.Moko_Kind IN ('21','22')
AND T.uriage_shime_status = '1'
--AND T.jisseki_sign = '1'
AND T.del_flg = '0'
)TAFD
WHERE TAFD.RN=1
AND TAFD.jisseki_sign = '1'
GROUP BY
to_char(tafd.ksai_date,'yyyyMM'),
tafd.Uriage_Sha_Code,
tafd.uriage_nengetsu,
tafd.Ksai_Sha_Code,
tafd.tanto_bu_ka_code,
tafd.gyosyu_mcode,
tafd.adv_baitai_bunrui_code,
tafd.uriage_shime_status,
tafd.del_flg,
tafd.karihan_sign,
tafd.jisseki_sign,
tafd.moko_kind;
