DROP MATERIALIZED VIEW WVIEW0081_YYYY;
DROP TABLE WVIEW0081_YYYY PURGE;
CREATE MATERIALIZED VIEW WVIEW0081_YYYY
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT B.EX_YYYY "VPERIOD"
,A.KEY1 "VKEY1" ,A.KEY2 "VKEY2" ,A.KEY3 "VKEY3" ,A.KEY4 "VKEY4" ,A.KEY5 "VKEY5" ,A.KEY6 "VKEY6" ,A.KEY7 "VKEY7" ,A.KEY8 "VKEY8" ,A.KEY9 "VKEY9" ,A.KEY10 "VKEY10" ,A.KEY11 "VKEY11" ,A.KEY12 "VKEY12" ,A.KEY13 "VKEY13" ,A.KEY14 "VKEY14" ,A.KEY15 "VKEY15"
,SUM(A.D1) "VD1" ,SUM(A.D2) "VD2" ,SUM(A.D3) "VD3" ,SUM(A.D4) "VD4" ,SUM(A.D5) "VD5" ,SUM(A.D6) "VD6" ,SUM(A.D7) "VD7" ,SUM(A.D8) "VD8"
,SUM(A.D9) "VD9" ,SUM(A.D10) "VD10" ,SUM(A.D11) "VD11" ,SUM(A.D12) "VD12" ,SUM(A.D13) "VD13" ,SUM(A.D14) "VD14" ,SUM(A.D15) "VD15"
,COUNT(*) "VCNTA",COUNT(A.D1) "VCNT1",COUNT(A.D2) "VCNT2",COUNT(A.D3) "VCNT3",COUNT(A.D4) "VCNT4",COUNT(A.D5) "VCNT5",COUNT(A.D6) "VCNT6",COUNT(A.D7) "VCNT7",COUNT(A.D8) "VCNT8"
,COUNT(A.D9) "VCNT9",COUNT(A.D10) "VCNT10",COUNT(A.D11) "VCNT11",COUNT(A.D12) "VCNT12",COUNT(A.D13) "VCNT13",COUNT(A.D14) "VCNT14",COUNT(A.D15) "VCNT15"
FROM EX_DATA20 A, EX_DAY B
WHERE A.DATE1 = B.EX_DATE
GROUP BY B.EX_YYYY
,A.KEY1 ,A.KEY2 ,A.KEY3 ,A.KEY4 ,A.KEY5 ,A.KEY6 ,A.KEY7 ,A.KEY8 ,A.KEY9 ,A.KEY10 ,A.KEY11 ,A.KEY12 ,A.KEY13 ,A.KEY14 ,A.KEY15 ;
DROP MATERIALIZED VIEW WVIEW0081_TERM;
DROP TABLE WVIEW0081_TERM PURGE;
CREATE MATERIALIZED VIEW WVIEW0081_TERM
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT B.EX_TERM "VPERIOD"
,A.KEY1 "VKEY1" ,A.KEY2 "VKEY2" ,A.KEY3 "VKEY3" ,A.KEY4 "VKEY4" ,A.KEY5 "VKEY5" ,A.KEY6 "VKEY6" ,A.KEY7 "VKEY7" ,A.KEY8 "VKEY8" ,A.KEY9 "VKEY9" ,A.KEY10 "VKEY10" ,A.KEY11 "VKEY11" ,A.KEY12 "VKEY12" ,A.KEY13 "VKEY13" ,A.KEY14 "VKEY14" ,A.KEY15 "VKEY15"
,SUM(A.D1) "VD1" ,SUM(A.D2) "VD2" ,SUM(A.D3) "VD3" ,SUM(A.D4) "VD4" ,SUM(A.D5) "VD5" ,SUM(A.D6) "VD6" ,SUM(A.D7) "VD7" ,SUM(A.D8) "VD8"
,SUM(A.D9) "VD9" ,SUM(A.D10) "VD10" ,SUM(A.D11) "VD11" ,SUM(A.D12) "VD12" ,SUM(A.D13) "VD13" ,SUM(A.D14) "VD14" ,SUM(A.D15) "VD15"
,COUNT(*) "VCNTA",COUNT(A.D1) "VCNT1",COUNT(A.D2) "VCNT2",COUNT(A.D3) "VCNT3",COUNT(A.D4) "VCNT4",COUNT(A.D5) "VCNT5",COUNT(A.D6) "VCNT6",COUNT(A.D7) "VCNT7",COUNT(A.D8) "VCNT8"
,COUNT(A.D9) "VCNT9",COUNT(A.D10) "VCNT10",COUNT(A.D11) "VCNT11",COUNT(A.D12) "VCNT12",COUNT(A.D13) "VCNT13",COUNT(A.D14) "VCNT14",COUNT(A.D15) "VCNT15"
FROM EX_DATA20 A, EX_DAY B
WHERE A.DATE1 = B.EX_DATE
GROUP BY B.EX_TERM
,A.KEY1 ,A.KEY2 ,A.KEY3 ,A.KEY4 ,A.KEY5 ,A.KEY6 ,A.KEY7 ,A.KEY8 ,A.KEY9 ,A.KEY10 ,A.KEY11 ,A.KEY12 ,A.KEY13 ,A.KEY14 ,A.KEY15 ;
DROP MATERIALIZED VIEW WVIEW0081_YYYYMM;
DROP TABLE WVIEW0081_YYYYMM PURGE;
CREATE MATERIALIZED VIEW WVIEW0081_YYYYMM
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT B.EX_YYYYMM "VPERIOD"
,A.KEY1 "VKEY1" ,A.KEY2 "VKEY2" ,A.KEY3 "VKEY3" ,A.KEY4 "VKEY4" ,A.KEY5 "VKEY5" ,A.KEY6 "VKEY6" ,A.KEY7 "VKEY7" ,A.KEY8 "VKEY8" ,A.KEY9 "VKEY9" ,A.KEY10 "VKEY10" ,A.KEY11 "VKEY11" ,A.KEY12 "VKEY12" ,A.KEY13 "VKEY13" ,A.KEY14 "VKEY14" ,A.KEY15 "VKEY15"
,SUM(A.D1) "VD1" ,SUM(A.D2) "VD2" ,SUM(A.D3) "VD3" ,SUM(A.D4) "VD4" ,SUM(A.D5) "VD5" ,SUM(A.D6) "VD6" ,SUM(A.D7) "VD7" ,SUM(A.D8) "VD8"
,SUM(A.D9) "VD9" ,SUM(A.D10) "VD10" ,SUM(A.D11) "VD11" ,SUM(A.D12) "VD12" ,SUM(A.D13) "VD13" ,SUM(A.D14) "VD14" ,SUM(A.D15) "VD15"
,COUNT(*) "VCNTA",COUNT(A.D1) "VCNT1",COUNT(A.D2) "VCNT2",COUNT(A.D3) "VCNT3",COUNT(A.D4) "VCNT4",COUNT(A.D5) "VCNT5",COUNT(A.D6) "VCNT6",COUNT(A.D7) "VCNT7",COUNT(A.D8) "VCNT8"
,COUNT(A.D9) "VCNT9",COUNT(A.D10) "VCNT10",COUNT(A.D11) "VCNT11",COUNT(A.D12) "VCNT12",COUNT(A.D13) "VCNT13",COUNT(A.D14) "VCNT14",COUNT(A.D15) "VCNT15"
FROM EX_DATA20 A, EX_DAY B
WHERE A.DATE1 = B.EX_DATE
GROUP BY B.EX_YYYYMM
,A.KEY1 ,A.KEY2 ,A.KEY3 ,A.KEY4 ,A.KEY5 ,A.KEY6 ,A.KEY7 ,A.KEY8 ,A.KEY9 ,A.KEY10 ,A.KEY11 ,A.KEY12 ,A.KEY13 ,A.KEY14 ,A.KEY15 ;
EXIT
