db2 视图1 例子一

SET SCHEMA EDWDBA;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDWDBA";

CREATE VIEW "DM1"."DIM_GRP_CDR_EVT_TYPE_DETAIL"
 ("CDR_EVT_CATE_ID",
  "CDR_EVT_CATE_NM",
  "CDR_EVENT_TYPE_ID",
  "CDR_EVENT_TYPE_NM",
  "SHOW_ORDER"
 )
  AS  SELECT DISTINCT 0 cdr_evt_cate_id, '总量' cdr_evt_cate_nm, A1.CDR_EVT_TP_ID 
    cdr_event_type_id, A1.CDR_EVT_TP_NM cdr_event_type_NM, 0 SHOW_ORDER
  FROM BML.CDR_EVT_TP_D A1, BML.CDR_EVT_TP_CATE_TREE A2
  WHERE a2.CATE_ID_1 = 680
    or (a2.cate_id_1=681
    and a2.cate_id_2 in (692,693,694))
    and A1.CDR_EVT_TP_CATE_ID=A2.CDR_EVT_TP_CATE_ID
UNION
SELECT DISTINCT 1 cdr_evt_cate_id, '市话' cdr_evt_cate_nm, A1.CDR_EVT_TP_ID 
    cdr_event_type_id, A1.CDR_EVT_TP_NM cdr_event_type_NM, 1 SHOW_ORDER
  FROM BML.CDR_EVT_TP_D A1, BML.CDR_EVT_TP_CATE_TREE A2
  WHERE a2.CATE_ID_1=680
    and A1.CDR_EVT_TP_CATE_ID=A2.CDR_EVT_TP_CATE_ID
UNION
SELECT DISTINCT 2 cdr_evt_cate_id, '国际长途' cdr_evt_cate_nm, 
    A1.CDR_EVT_TP_ID cdr_event_type_id, A1.CDR_EVT_TP_NM cdr_event_type_NM, 2 
    SHOW_ORDER
  FROM BML.CDR_EVT_TP_D A1, BML.CDR_EVT_TP_CATE_TREE A2
  WHERE a2.CATE_ID_1=681
    and a2.CATE_ID_2=693
    and A1.CDR_EVT_TP_CATE_ID=A2.CDR_EVT_TP_CATE_ID
UNION
SELECT DISTINCT 3 cdr_evt_cate_id, '国内长途' cdr_evt_cate_nm, 
    A1.CDR_EVT_TP_ID cdr_event_type_id, A1.CDR_EVT_TP_NM cdr_event_type_NM, 3 
    SHOW_ORDER
  FROM BML.CDR_EVT_TP_D A1, BML.CDR_EVT_TP_CATE_TREE A2
  WHERE a2.CATE_ID_1=681
    and a2.CATE_ID_2=692
    and A1.CDR_EVT_TP_CATE_ID=A2.CDR_EVT_TP_CATE_ID
UNION
SELECT DISTINCT 4 cdr_evt_cate_id, '港澳台长途' cdr_evt_cate_nm, 
    A1.CDR_EVT_TP_ID cdr_event_type_id, A1.CDR_EVT_TP_NM cdr_event_type_NM, 4 
    SHOW_ORDER
  FROM BML.CDR_EVT_TP_D A1, BML.CDR_EVT_TP_CATE_TREE A2
  WHERE a2.CATE_ID_1=681
    and a2.CATE_ID_2=694
    and A1.CDR_EVT_TP_CATE_ID=A2.CDR_EVT_TP_CATE_ID
UNION
SELECT DISTINCT 6 cdr_evt_cate_id, 'IP接入费' cdr_evt_cate_nm, 
    A1.CDR_EVT_TP_ID cdr_event_type_id, A1.CDR_EVT_TP_NM cdr_event_type_NM, 6 
    SHOW_ORDER
  FROM BML.CDR_EVT_TP_D A1, BML.CDR_EVT_TP_CATE_TREE A2
  WHERE a2.CATE_ID_1=681
    and a2.CATE_ID_3 in (735,739,754)
    and A1.CDR_EVT_TP_CATE_ID=A2.CDR_EVT_TP_CATE_ID;

GRANT CONTROL ON TABLE "DM1"."DIM_GRP_CDR_EVT_TYPE_DETAIL" TO USER "EDWDBA";

 

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


SET SCHEMA EDWDBA;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDWDBA";

CREATE VIEW "DM1"."DIM_GRP_CUSTOMER_GROUP"
 ("DETAIL_CUSTOMER_GROUP_ID",
  "DETAIL_CUSTOMER_GROUP_NAME",
  "CUSTOMER_GROUP_ID",
  "CUSTOMER_GROUP_NAME",
  "SHOW_ORDER"
 )
  AS  SELECT DISTINCT A0.CUST_TP_ID DETAIL_CUSTOMER_GROUP_ID, A0.CUST_TP_NM 
    DETAIL_CUSTOMER_GROUP_NAME,
    case
      when A0.CUST_TP_ID_1 = 8
        then 200013
      when A0.CUST_TP_ID_1 = 7
        then 200014
      when A0.CUST_TP_ID_1 = 6
        then 200015
      else 2000151
    end as CUSTOMER_GROUP_ID, A0.CUST_TP_NM_1 CUSTOMER_GROUP_NAME,
    case
      when A0.CUST_TP_ID_1 = 8
        then 1
      when A0.CUST_TP_ID_1 = 7
        then 2
      when A0.CUST_TP_ID_1 = 6
        then 3
      else 4
    end as SHOW_ORDER
  FROM DM1.CUST_TP_D A0
UNION
SELECT -1 as DETAIL_CUSTOMER_GROUP_ID,'未知' as DETAIL_CUSTOMER_GROUP_NAME,
    2000151 as CUSTOMER_GROUP_ID,'其他客户' as CUSTOMER_GROUP_NAME,4 
    SHOW_ORDER
  FROM sysibm.sysdummy1;

GRANT SELECT ON TABLE "DM1"."DIM_GRP_CUSTOMER_GROUP" TO USER "EDWDBA";


!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SET SCHEMA EDWDBA;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDWDBA";

CREATE VIEW "DM1"."DIM_GRP_PD_SPEC_USER"
  AS  SELECT DISTINCT
    CASE
      WHEN CATE_ID_1 = 1117
        OR CATE_ID_2 =1135
        THEN 1
      WHEN CATE_ID_1 in (1119,1927)
        THEN 2
      WHEN CATE_ID_1 = 1115
        THEN 3
      ELSE 4
    END AS PD_SPEC_CATE_ID,
    CASE
      WHEN CATE_ID_1 = 1117
        OR CATE_ID_2 =1135
        THEN '固话'
      WHEN CATE_ID_1 in (1119,1927)
        THEN '宽带'
      WHEN CATE_ID_1 = 1115
        THEN '移动'
      ELSE '其他'
    END AS PD_SPEC_CATE_NAME, PD_SPEC_ID AS PD_SPEC_ID, T.PD_SPEC_NM AS 
    PD_SPEC_NM,
    CASE
      WHEN CATE_ID_1 = 1117
        OR CATE_ID_2 =1135
        THEN 1
      WHEN CATE_ID_1 in (1119,1927)
        THEN 2
      WHEN CATE_ID_1 = 1115
        THEN 3
      ELSE 4
    END AS SHOW_ORDER
  FROM (
    SELECT T.PD_SPEC_ID,T.PD_SPEC_NM,S.CATE_ID_1,S.CATE_ID_2
      FROM DM1.PD_SPEC_D T
        LEFT JOIN DM1.PD_SPEC_CATE_TREE S
        ON T.PD_SPEC_CATE_ID = S.PD_SPEC_CATE_ID ) T;

GRANT CONTROL ON TABLE "DM1"."DIM_GRP_PD_SPEC_USER" TO USER "EDWDBA";

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


SET SCHEMA EDWDBA;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDWDBA";

CREATE VIEW "DM1"."DIM_MOBILE_SERVICE_MAPPING"
 ("STD_SERVICE_SPEC_ID",
  "STD_SERVICE_SPEC_NAME",
  "EDW_SERVICE_SPEC_ID",
  "EDW_SERVICE_SPEC_NAME",
  "MAPPING_TYPE"
 )
  AS  select 2,'短信',T.ACCT_ITEM_TP_ID,ACCT_TP_NM,2
  from bml.acct_item_tp_d t
  where acct_item_tp_cate_id in (
      select acct_item_tp_cate_id
        from bml.acct_item_tp_cate_tree t
        where cate_id_1 = 1982
          and cate_id_5 in (2149,2150,2151,2255,2274,2280,2301,2308,2521) )
UNION
select 1,'来电显示',T.ACCT_ITEM_TP_ID,ACCT_TP_NM,2
  from bml.acct_item_tp_d t
  where acct_item_tp_cate_id in (
      select acct_item_tp_cate_id
        from bml.acct_item_tp_cate_tree t
        where cate_id_1 = 1982
          and cate_id_4 = 2141 )
UNION
select 4,'七彩铃音',T.ACCT_ITEM_TP_ID,ACCT_TP_NM,2
  from bml.acct_item_tp_d t
  where acct_item_tp_cate_id in (
      select acct_item_tp_cate_id
        from bml.acct_item_tp_cate_tree t
        where cate_id_1 = 1982
          and cate_id_5 = 2305 )

----2305 '广告彩铃业务收入'
  UNION
select 5,'手机报',T.ACCT_ITEM_TP_ID,ACCT_TP_NM,2
  from bml.acct_item_tp_d t
  where acct_item_tp_cate_id in (
      select acct_item_tp_cate_id
        from bml.acct_item_tp_cate_tree t
        where cate_id_1 = 1982
          and cate_id_5 in (2521,2522,2523))
UNION
select 6,'189邮箱',T.ACCT_ITEM_TP_ID,ACCT_TP_NM,2
  from bml.acct_item_tp_d t
  where acct_item_tp_cate_id in (
      select acct_item_tp_cate_id
        from bml.acct_item_tp_cate_tree t
        where cate_id_1 = 1982
          and cate_id_5 = 2471 )
UNION
select 3,'彩信',T.ACCT_ITEM_TP_ID,ACCT_TP_NM,2
  from bml.acct_item_tp_d t
  where acct_item_tp_cate_id in (
      select acct_item_tp_cate_id
        from bml.acct_item_tp_cate_tree t
        where cate_id_1 = 1982
          and cate_id_4 in (2462,2473,2474))
union
select 7,'手机上网',T.ACCT_ITEM_TP_ID,ACCT_TP_NM,2
  from bml.acct_item_tp_d t
  where acct_item_tp_cate_id in (
      select acct_item_tp_cate_id
        from bml.acct_item_tp_cate_tree t
        where cate_id_1 = 1981
          and cate_id_4 = 2425 )
union
select 8,'手机下载',T.ACCT_ITEM_TP_ID,ACCT_TP_NM,2
  from bml.acct_item_tp_d t
  where acct_item_tp_cate_id in (
      select acct_item_tp_cate_id
        from bml.acct_item_tp_cate_tree t
        where cate_id_1 = 1982
          and cate_id_5 in (2543,2466) )
union
select 1,'来电显示',SVC_SPEC_ID,SVC_SPEC_NM,1
  from BML.SVC_SPEC_TP_D t
  where svc_spec_id_1 = 1088
UNION
select 2,'短信',SVC_SPEC_ID,SVC_SPEC_NM,1
  from BML.SVC_SPEC_TP_D t
  where svc_spec_id = 701
UNION
select 4,'七彩铃音',SVC_SPEC_ID,SVC_SPEC_NM,1
  from BML.SVC_SPEC_TP_D t
  where svc_spec_id_2 in (41,651)
UNION
select 5,'手机报',SVC_SPEC_ID,SVC_SPEC_NM,1
  from BML.SVC_SPEC_TP_D t
  where svc_spec_id_1 in (1943)
UNION
select 6,'189邮箱',SVC_SPEC_ID,SVC_SPEC_NM,1
  from BML.SVC_SPEC_TP_D t
  where svc_spec_id in (746,747,748);

GRANT CONTROL ON TABLE "DM1"."DIM_MOBILE_SERVICE_MAPPING" TO USER "EDWDBA";


!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


SET SCHEMA EDWDBA;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDWDBA";

CREATE VIEW "DM1"."DIM_PACKAGE_DIV"
  AS  select distinct s.area_id AS AREA_ID,
    case
      when cate_id_5 in (203012201,203012202)
        then cate_id_5
      else cate_id_4
    end AS PACK_ID , s.po_spec_id AS DIV_ID, po_spec_nm AS DIV_ORIGIN_NAME, 
    div_name AS DIV_NAME, row_number() over(partition by
    case
      when cate_id_5 in (203012201,203012202)
        then cate_id_5
      else cate_id_4
    end order by s.area_id,d.po_spec_id) AS SHOW_ORDER
  from BML.PO_SPEC_CATE_TREE t, bml.po_spec_d d, DM1.DIM_PO_SPEC_DIV s
  where t.po_spec_cate_id = d.dflt_po_spec_cate_id
    and d.po_spec_id = s.po_spec_id
    and cate_id_4 in (1010103,1010301,1010302,2030101,2030102,2030105,2030103,
      2030122);

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

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";