db2 視圖 3

 SET SCHEMA EDWDBA;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDWDBA";CREATE VIEW "DM1"."V_DIM_ACCT_TYPE_GRP_QYW"  AS  SELECT 1 ACCT_TYPE_ID,'全業務收入' ACCT_TYPE_NAME,A1.ACCT_ITEM_TP_ID     DETAIL_ACCT_TYPE_ID,A1.ACCT_TP_NM DETAIL_ACCT_TYPE_NAME,1 SHOW_ORDER  from BML.ACCT_ITEM_TP_D A1UNIONselect    CASE      WHEN A0.CATE_ID_1 IN (1,28)        THEN 2      --固話收入      WHEN A0.CATE_ID_1 = 28        THEN 3      --月租      WHEN A0.CATE_ID_1 = 1        THEN 4      --通話收入      WHEN A0.CATE_ID_2 = 5        THEN 5      --本地      WHEN A0.CATE_ID_3 = 31        THEN 6      --國內長途      WHEN A0.CATE_ID_3 = 32        THEN 7      --國際長途      WHEN A0.CATE_ID_3 = 33        THEN 8      --港澳台長途      WHEN A0.CATE_ID_2 = 7        THEN 9      --互連網收入      WHEN A0.CATE_ID_2 = 8        THEN 10      --資源收入      WHEN A0.CATE_ID_1 = 3        THEN 11      --增值及綜合資訊收入      WHEN A0.CATE_ID_1 = 1000        THEN 12      --移動收入      WHEN A0.CATE_ID_3 = 1110        THEN 13      --移動月租收入      WHEN A0.CATE_ID_3 = 1120        THEN 14      --移動通話費收入      WHEN A0.CATE_ID_3 = 1200        THEN 15      --移動增值收入      WHEN A0.CATE_ID_3 = 1400        THEN 16      --移動其它收入      ELSE 17    END ACCT_TYPE_ID,    CASE      WHEN A0.CATE_ID_1 IN (1,28)        THEN '  固話收入'      WHEN A0.CATE_ID_1 = 28        THEN '    月租'      WHEN A0.CATE_ID_1 = 1        THEN '    通話費收入'      WHEN A0.CATE_ID_2 = 5        THEN '　　　本地'      WHEN A0.CATE_ID_3 = 31        THEN '　　　國內長途'      WHEN A0.CATE_ID_3 = 32        THEN '　　　國際長途'      WHEN A0.CATE_ID_3 = 33        THEN '　　　港澳台長途'      WHEN A0.CATE_ID_2 = 7        THEN '　互連網收入'      WHEN A0.CATE_ID_2 = 8        THEN '　資源出租'      WHEN A0.CATE_ID_1 = 3        THEN '　增值及綜合資訊收入'      WHEN A0.CATE_ID_1 = 1000        THEN '　移動收入'      WHEN A0.CATE_ID_3 = 1110        THEN '　　移動月租收入'      WHEN A0.CATE_ID_3 = 1120        THEN '　　移動通話費收入'      WHEN A0.CATE_ID_3 = 1200        THEN '　　移動增值收入'      WHEN A0.CATE_ID_3 = 1400        THEN '　　移動其他收入'      ELSE '  其他收入'    END ACCT_TYPE_NAME, A1.ACCT_ITEM_TP_ID DETAIL_ACCT_TYPE_ID, A1.ACCT_TP_NM     DETAIL_ACCT_TYPE_NAME,    CASE      WHEN A0.CATE_ID_1 IN (1,28)        THEN 2      --固話收入      WHEN A0.CATE_ID_1 = 28        THEN 3      --月租      WHEN A0.CATE_ID_1 = 1        THEN 4      --通話收入      WHEN A0.CATE_ID_2 = 5        THEN 5      --本地      WHEN A0.CATE_ID_3 = 31        THEN 6      --國內長途      WHEN A0.CATE_ID_3 = 32        THEN 7      --國際長途      WHEN A0.CATE_ID_3 = 33        THEN 8      --港澳台長途      WHEN A0.CATE_ID_2 = 7        THEN 9      --互連網收入      WHEN A0.CATE_ID_2 = 8        THEN 10      --資源收入      WHEN A0.CATE_ID_1 = 3        THEN 11      --增值及綜合資訊收入      WHEN A0.CATE_ID_1 = 1000        THEN 12      --移動收入      WHEN A0.CATE_ID_3 = 1110        THEN 13      --移動月租收入      WHEN A0.CATE_ID_3 = 1120        THEN 14      --移動通話費收入      WHEN A0.CATE_ID_3 = 1200        THEN 15      --移動增值收入      WHEN A0.CATE_ID_3 = 1400        THEN 16      --移動其它收入      ELSE 17    END SHOW_ORDER  from BML.ACCT_ITEM_TP_D A1    LEFT OUTER JOIN BML.ACCT_ITEM_TP_CATE_TREE A0    ON A0.ACCT_ITEM_TP_CATE_ID=A1.ACCT_ITEM_TP_ID;GRANT CONTROL ON TABLE "DM1"."V_DIM_ACCT_TYPE_GRP_QYW" TO USER "EDWDBA";!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!SET SCHEMA EDWDBA;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDWDBA";CREATE VIEW "DM1"."V_DIM_ACCT_TYPE_GRP_TEL"  AS  SELECT 1 AS ACCT_TYPE_ID, '合計' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID,     T.ACCT_TP_NM,1 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_1 <> 1000UNION ALLSELECT 2 AS ACCT_TYPE_ID,'  月租與通話費' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID,    T.ACCT_TP_NM,2 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_1 IN (1,28)UNION ALLSELECT 3 AS ACCT_TYPE_ID,'    月租' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID,     T.ACCT_TP_NM,3 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_1 = 28UNION ALLSELECT 4 AS ACCT_TYPE_ID,'    通話費收入' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID,    T.ACCT_TP_NM,4 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_1 = 1UNION ALLSELECT 5 AS ACCT_TYPE_ID,'　　　本地' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID,     T.ACCT_TP_NM,5 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_2 = 5UNION ALLSELECT 6 AS ACCT_TYPE_ID, '　　　國內長途' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID    ,T.ACCT_TP_NM,6 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_3 = 31UNION ALLSELECT 7 AS ACCT_TYPE_ID,'　　　國際長途' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID,    T.ACCT_TP_NM,7 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_3 = 32UNION ALLSELECT 8 AS ACCT_TYPE_ID,'　　　港澳台長途' AS ACCT_TYPE_NAME,     T.ACCT_ITEM_TP_ID,T.ACCT_TP_NM,8 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_3 = 33UNION ALLSELECT 9 AS ACCT_TYPE_ID,'　互連網收入' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID,     T.ACCT_TP_NM,9 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_2 = 7UNION ALLSELECT 10 AS ACCT_TYPE_ID,'　資源出租' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID,     T.ACCT_TP_NM,10 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_2 = 8UNION ALLSELECT 11 AS ACCT_TYPE_ID,'　增值及綜合資訊收入' AS ACCT_TYPE_NAME,     T.ACCT_ITEM_TP_ID,T.ACCT_TP_NM ,11 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND S.CATE_ID_1 = 3UNION ALLSELECT 12 AS ACCT_TYPE_ID,'  其他' AS ACCT_TYPE_NAME,T.ACCT_ITEM_TP_ID,     T.ACCT_TP_NM,12 AS SHOW_ORDER  FROM BML.ACCT_ITEM_TP_D T    INNER JOIN BML.ACCT_ITEM_TP_CATE_TREE S    ON T.ACCT_ITEM_TP_CATE_ID = S.ACCT_ITEM_TP_CATE_ID    AND (S.CATE_ID_1 NOT IN (1000,1,28,3,2))UNION ALLSELECT -1 AS ACCT_TYPE_ID,'未知' AS ACCT_TYPE_NAME,-1 AS ACCT_ITEM_TP_ID,     '未知' AS ACCT_TP_NM,13 AS SHOW_ORDER  FROM sysibm.sysdummy1;GRANT SELECT ON TABLE "DM1"."V_DIM_ACCT_TYPE_GRP_TEL" TO USER "CAODM";GRANT SELECT ON TABLE "DM1"."V_DIM_ACCT_TYPE_GRP_TEL" TO USER "EDWDBA"; !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!SET SCHEMA EDWDBA;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDWDBA";CREATE VIEW "DM1"."V_DIM_CITY_AREA_LO_MAP"  AS  select 'A' city_id, 1 ods_city_id, '南京' city_name, '025' city_code, '210000'    zip_code, 3 admin_lo_id, 'IPD_IN_NJ' IPD_IN_USER, '寧' SNAME, 25 ID_PREFIX    , 250 LATN_ID  from sysibm.sysdummy1unionselect 'B' city_id, 2 ods_city_id, '無錫' city_name, '0510' city_code,     '214000' zip_code, 15 admin_lo_id, 'IPD_IN_WX' IPD_IN_USER, '錫' SNAME, 10    ID_PREFIX, 510 LATN_ID  from sysibm.sysdummy1unionselect 'C' city_id, 3 ods_city_id, '鎮江' city_name, '0511' city_code,     '212000' zip_code, 4 admin_lo_id, 'IPD_IN_ZJ' IPD_IN_USER, '鎮' SNAME, 40     ID_PREFIX, 511 LATN_ID  from sysibm.sysdummy1unionselect 'D' city_id, 4 ods_city_id, '蘇州' city_name, '0512' city_code,     '215000' zip_code, 20 admin_lo_id, 'IPD_IN_SZ' IPD_IN_USER, '蘇' SNAME, 12    ID_PREFIX, 512 LATN_ID  from sysibm.sysdummy1unionselect 'E' city_id, 5 ods_city_id, '南通' city_name, '0513' city_code,     '226300' zip_code, 26 admin_lo_id, 'IPD_IN_NT' IPD_IN_USER, '通' SNAME, 13    ID_PREFIX, 513 LATN_ID  from sysibm.sysdummy1unionselect 'F' city_id, 6 ods_city_id, '揚州' city_name, '0514' city_code,     '225000' zip_code, 33 admin_lo_id, 'IPD_IN_YZ' IPD_IN_USER, '揚' SNAME, 14    ID_PREFIX, 514 LATN_ID  from sysibm.sysdummy1unionselect 'G' city_id, 7 ods_city_id, '鹽城' city_name, '0515' city_code,     '224000' zip_code, 39 admin_lo_id, 'IPD_IN_YC' IPD_IN_USER, '鹽' SNAME, 15    ID_PREFIX, 515 LATN_ID  from sysibm.sysdummy1unionselect 'H' city_id, 8 ods_city_id, '徐州' city_name, '0516' city_code,     '221000' zip_code, 48 admin_lo_id, 'IPD_IN_XZ' IPD_IN_USER, '徐' SNAME, 16    ID_PREFIX, 516 LATN_ID  from sysibm.sysdummy1unionselect 'I' city_id, 9 ods_city_id, '淮安' city_name, '0517' city_code,     '223200' zip_code, 60 admin_lo_id, 'IPD_IN_HA' IPD_IN_USER, '淮' SNAME, 17    ID_PREFIX, 517 LATN_ID  from sysibm.sysdummy1unionselect 'J' city_id, 10 ods_city_id, '連雲港' city_name, '0518' city_code,     '222000' zip_code, 63 admin_lo_id, 'IPD_IN_LYG' IPD_IN_USER, '連' SNAME,     18 ID_PREFIX, 518 LATN_ID  from sysibm.sysdummy1unionselect 'K' city_id, 11 ods_city_id, '常州' city_name, '0519' city_code,     '213000' zip_code, 69 admin_lo_id, 'IPD_IN_CZ' IPD_IN_USER, '常' SNAME, 19    ID_PREFIX, 519 LATN_ID  from sysibm.sysdummy1unionselect 'L' city_id, 12 ods_city_id, '泰州' city_name, '0523' city_code,     '225300' zip_code, 79 admin_lo_id, 'IPD_IN_TZ' IPD_IN_USER, '泰' SNAME, 23    ID_PREFIX, 523 LATN_ID  from sysibm.sysdummy1unionselect 'M' city_id, 13 ods_city_id, '宿遷' city_name, '0527' city_code,     '223800' zip_code, 84 admin_lo_id, 'IPD_IN_SQ' IPD_IN_USER, '宿' SNAME, 27    ID_PREFIX, 527 LATN_ID  from sysibm.sysdummy1unionselect 'Z' city_id, 0 ods_city_id, '全省' city_name, cast(null as char(2))     city_code, cast(null as char(2)) zip_code, 2 admin_lo_id, 'IPD_IN_QS'     IPD_IN_USER, '省' SNAME, cast(null as int) ID_PREFIX, 8 LATN_ID  from sysibm.sysdummy1;GRANT SELECT ON TABLE "DM1"."V_DIM_CITY_AREA_LO_MAP" TO USER "EDWDBA";