CREATE TABLE FACT
(
AMT NUMBER
, PR VARCHAR2(20)
, BU VARCHAR2(20)
, AC VARCHAR2(20),
CONSTRAINT FACT_PK PRIMARY KEY (PR,BU,AC))
ORGANIZATION index
Table created.
CREATE TABLE "PR"
( "PR" VARCHAR2(20 BYTE),
"SEL" VARCHAR2(20 BYTE)
,
CONSTRAINT PR_PK PRIMARY KEY (PR))
ORGANIZATION index
Table created.
CREATE TABLE "BU"
( "BU" VARCHAR2(20 BYTE)
,
CONSTRAINT BU_PK PRIMARY KEY (BU))
ORGANIZATION index
Table created.
CREATE TABLE "AC"
( "AC" VARCHAR2(20 BYTE)
,
CONSTRAINT AC_PK PRIMARY KEY (AC))
ORGANIZATION index
Table created.
INSERT INTO "PR" (PR,SEL) VALUES ('201601','A')
1 row(s) inserted.
INSERT INTO "PR" (PR,SEL) VALUES ('201602','A')
1 row(s) inserted.
INSERT INTO "PR" (PR,SEL) VALUES ('201603','A')
1 row(s) inserted.
INSERT INTO "PR" (PR,SEL) VALUES ('201604','B')
1 row(s) inserted.
INSERT INTO "PR" (PR,SEL) VALUES ('201605','C')
1 row(s) inserted.
INSERT INTO "PR" (PR,SEL) VALUES ('201606','A')
1 row(s) inserted.
INSERT INTO "PR" (PR,SEL) VALUES ('201607','A')
1 row(s) inserted.
INSERT INTO "PR" (PR,SEL) VALUES ('201608','A')
1 row(s) inserted.
INSERT INTO "PR" (PR,SEL) VALUES ('201609','A')
1 row(s) inserted.
INSERT INTO "BU" (BU) VALUES ('BU1')
1 row(s) inserted.
INSERT INTO "AC" (AC) VALUES ('AC1')
1 row(s) inserted.
INSERT INTO "FACT" (AMT, PR, BU, AC) VALUES ('100', '201601', 'BU1', 'AC1')
1 row(s) inserted.
INSERT INTO "FACT" (AMT, PR, BU, AC) VALUES ('100', '201602', 'BU1', 'AC1')
1 row(s) inserted.
INSERT INTO "FACT" (AMT, PR, BU, AC) VALUES ('100', '201603', 'BU1', 'AC1')
1 row(s) inserted.
INSERT INTO "FACT" (AMT, PR, BU, AC) VALUES ('200', '201604', 'BU1', 'AC1')
1 row(s) inserted.
INSERT INTO "FACT" (AMT, PR, BU, AC) VALUES ('200', '201605', 'BU1', 'AC1')
1 row(s) inserted.
INSERT INTO "FACT" (AMT, PR, BU, AC) VALUES ('200', '201606', 'BU1', 'AC1')
1 row(s) inserted.
INSERT INTO "FACT" (AMT, PR, BU, AC) VALUES ('300', '201607', 'BU1', 'AC1')
1 row(s) inserted.
INSERT INTO "FACT" (AMT, PR, BU, AC) VALUES ('300', '201608', 'BU1', 'AC1')
1 row(s) inserted.
INSERT INTO "FACT" (AMT, PR, BU, AC) VALUES ('300', '201609', 'BU1', 'AC1')
1 row(s) inserted.
COMMIT
Statement processed.
CREATE materialized VIEW MAINVW("AMOUNT","PR","BU","AC")
AS
WITH INNR AS (
SELECT SUM(AMT) AS AMOUNT,F.PR AS PR, F.BU AS BU, F.AC AS AC,PR.SEL AS SEL FROM FACT F,PR,BU,AC WHERE F.PR=PR.PR AND F.BU=BU.BU AND F.AC=AC.AC
GROUP BY F.PR,F.BU,F.AC,PR.SEL),
OUTR AS
(SELECT LEAD(AMOUNT,1, NULL) OVER (PARTITION BY BU,AC ORDER BY PR) AS NXTMONTH,
LAG(AMOUNT,1, NULL) OVER (PARTITION BY BU,AC ORDER BY PR) AS PRIORMONTH,
AMOUNT AS CURRMO,
PR,BU,AC,SEL FROM INNR)
SELECT CASE WHEN SEL='A' THEN CURRMO
WHEN SEL='B' THEN PRIORMONTH
ELSE NXTMONTH
END AS AMOUNT, PR,BU,AC FROM OUTR
Statement processed.