【BIEE】04_当维度表中的维不存在事实表中,需要展示所有维度并且数据类展示为0

--新建维度表
  DROP TABLE DIM_LEVEL;
  CREATE TABLE DIM_LEVEL(ELEVEL VARCHAR(2) NOT NULL,
                         LEVEL_DEC VARCHAR(10),
                         DETAIL_DESC VARCHAR(100),
                         PRIMARY KEY (ELEVEL));
  INSERT INTO DIM_LEVEL VALUES ('A', '优秀', '工资大于等于2000');

  INSERT INTO DIM_LEVEL VALUES ('B', '良好', '工资大于等于1500且小于2000');

  INSERT INTO DIM_LEVEL VALUES ('C', '一般', '工资小于1500');
  ------新建表
  DROP TABLE EMP_FACT;
  CREATE TABLE EMP_FACT(CUST_ID VARCHAR(40) NOT NULL ,
                        CUST_NAME VARCHAR(80),
                        SAL INT,
                        ELEVEL VARCHAR(2),
                        PRIMARY KEY (CUST_ID),
                        constraint FK_ELEVEL FOREIGN KEY (ELEVEL) REFERENCES DIM_LEVEL(ELEVEL));
  INSERT INTO EMP_FACT VALUES ('C001', '牧云笙', 6000, 'A');
  INSERT INTO EMP_FACT VALUES ('C002', '牧云勤', 10000, 'A');
  INSERT INTO EMP_FACT VALUES ('C003', '牧云陆', 1700, 'B');
  INSERT INTO EMP_FACT VALUES ('C004', '寒江', 9000, 'A');
  INSERT INTO EMP_FACT VALUES ('C005', '穆如天彤', 10000, 'A');
  COMMIT;

ALTER TABLE DIM_LEVEL ADD SEQ INT;
UPDATE DIM_LEVEL A SET SEQ=(SELECT CASE WHEN ELEVEL='A' THEN 1 WHEN ELEVEL='B' THEN 2 WHEN ELEVEL='C' THEN 3 ELSE NULL END
FROM DIM_LEVEL B WHERE A.ELEVEL=B.ELEVEL)
COMMIT;