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