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 A1
UNION
select
    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 <> 1000
UNION ALL
SELECT 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 ALL
SELECT 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 = 28
UNION ALL
SELECT 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 = 1
UNION ALL
SELECT 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 = 5
UNION ALL
SELECT 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 = 31
UNION ALL
SELECT 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 = 32
UNION ALL
SELECT 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 = 33
UNION ALL
SELECT 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 = 7
UNION ALL
SELECT 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 = 8
UNION ALL
SELECT 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 = 3
UNION ALL
SELECT 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 ALL
SELECT -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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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.sysdummy1
union
select '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";