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_IDUNIONSELECT 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_IDUNIONSELECT 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_IDUNIONSELECT 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_IDUNIONSELECT 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_IDUNIONSELECT 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 A0UNIONSELECT -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) )UNIONselect 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 )UNIONselect 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 '廣告彩鈴業務收入'  UNIONselect 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))UNIONselect 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 )UNIONselect 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))unionselect 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 )unionselect 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) )unionselect 1,'來電顯示',SVC_SPEC_ID,SVC_SPEC_NM,1  from BML.SVC_SPEC_TP_D t  where svc_spec_id_1 = 1088UNIONselect 2,'簡訊',SVC_SPEC_ID,SVC_SPEC_NM,1  from BML.SVC_SPEC_TP_D t  where svc_spec_id = 701UNIONselect 4,'七彩鈴音',SVC_SPEC_ID,SVC_SPEC_NM,1  from BML.SVC_SPEC_TP_D t  where svc_spec_id_2 in (41,651)UNIONselect 5,'手機報',SVC_SPEC_ID,SVC_SPEC_NM,1  from BML.SVC_SPEC_TP_D t  where svc_spec_id_1 in (1943)UNIONselect 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 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";