DB2 view 1 Example 1

Set schema edwdba; set current path = "sysibm", "sysfun", "sysproc", "sysibmadm", "edwdba"; Create View "DM1 ". "outputs" ("outputs", "outputs", "cdr_event_type_id", "cdr_event_type_nm", "show_order") as select distinct 0 values, 'Total' values, 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. when 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 _ 1_distinct 1 cdr_evt_cate_id, 'location '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_ca Te_id = a2.cdr _ nvidistinct 2 ←, 'International long' 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. listen A2 where a2.cate _ id_1 = 681 and a2.cate _ id_2 = 693 and a1.cdr _ comment = a2.cdr _ comment distinct 3 cdr_evt_cate_id, 'domestic long distance 'cdr_evt_cate_nm, a1.cdr _ evt_tp_ I D cdr_event_type_id, a1.cdr _ evt_tp_nm cdr_event_type_nm, 3 show_order from BML. cdr_evt_tp_d A1, BML. when A2 where a2.cate _ id_1 = 681 and a2.cate _ id_2 = 692 and a1.cdr _ frequency = a2.cdr _ 1_distinct 4 cdr_evt_cate_id, 'Hong Kong, Macao, Taiwan long distance 'segment, a1.cdr _ evt_tp_id cdr_event, a1.cdr _ evt_tp_nm cdr_event_type_nm, 4 show_order from BML. cdr_evt_tp_d A1, BML. cdr_e Vt_tp_cate_tree A2 where a2.cate _ id_1 = 681 and a2.cate _ id_2 = 694 and a1.cdr _ bandwidth = a2.cdr _ nvidistinct 6 bandwidth, 'IP access payby' bandwidth, 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 ". "partition" ("partition", "detail_customer_group_name", "customer_group_id", "customer_group_name", "show_order") as select distinct a0.cust _ tp_id limit, a0.cust _ tp_nm limit, case when a0.cust _ tp_id_1 = 8 then 200013 when a0.cus T_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 hour _ hour = 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 hour, 'unknown 'As detail_customer_group_name, 2000151 as customer_group_id, 'other customer' 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 ". "average" 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 'fixed then' when cate_id_1 in (1119,1927) then' broadband 'when Cate _ Id_1 = 1115 then' move 'else' other '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 (bytes) 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, 'sms ', 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_t P_cate_tree t where cate_id_1 = 1982 and cate_id_5 in (2149,2150, 2151,2255, 2274,2280, 2301,2308, 2521) unionselect 1, 'Incoming call display', 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, 'colorful alicloud', T. acct_item_tp_id, acct_tp_nm, 2 from BML. acct_item_tp_d t whe Re 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 'advertising ringtone business revenue 'unionselect 5, 'mobile phone report', 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, '1 89 mailbox ', 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, 'mms ', 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 C Ate_id_4 in (2462,2473, 2474) unionselect 7, 'Surfing the Internet via cell phone ', 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, 'Download by phone ', 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_it Em_tp_cate_tree t where cate_id_1 = 1982 and cate_id_5 in (, 2466) unionselect 1, 'Incoming call', svc_spec_id, svc_spec_nm, 1 from BML. svc_spec_tp_d t where svc_spec_id_1 = 1088 unionselect 2, 'sms ', svc_spec_id, svc_spec_nm, 1 from BML. svc_spec_tp_d t where svc_spec_id = 701 unionselect 4, 'colorful phones', svc_spec_id, svc_spec_nm, 1 from BML. svc_spec_tp_d t where svc_spec_id_2 in (41,651) unionselect 5, 'cell phone report', svc_spec_id, SVC _ Spec_nm, 1 from BML. svc_spec_tp_d t where svc_spec_id_1 in (1943) unionselect 6, 'mailbox 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,2030 12202) 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, 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,203 0122 );!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Set schema edwdba; set current path = "sysibm", "sysfun", "sysproc", "sysibmadm", "edwdba"; Create View "DM1 ". "Category" as select 1 acct_type_id, 'full business revenue '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 -- fixed-line income when a0.cate _ id_1 = 28 then 3 -- monthly rent when a0.cate _ I D_1 = 1 then 4 -- call income when a0.cate _ id_2 = 5 then 5 -- local when a0.cate _ id_3 = 31 then 6 -- domestic long distance when a0.cate _ id_3 = 32 then 7 -- international long distance when a0.cate _ id_3 = 33 then 8 -- Hong Kong, Macao and Taiwan long-distance when a0.cate _ id_2 = 7 then 9 -- Internet revenue when a0.cate _ id_2 = 8 then 10 -- resource income when a0.cate _ id_1 = 3 th 11 -- value-added and comprehensive information income when a0.cate _ id_1 = 1000 then 12 -- mobile income when a0.cate _ id_3 = 1110 then 13 -- Mobile monthly rental income when a0.cate _ id_3 = 1120 then 14- -Mobile Phone bill income when a0.cate _ id_3 = 1200 then 15 -- mobile value-added income when a0.cate _ id_3 = 1400 then 16 -- Mobile other income else 17 end acct_type_id, case when a0.cate _ id_1 in (1, 28) then 'Fixed-line income 'When a0.cate _ id_1 = 28 then' monthly rent 'when a0.cate _ id_1 = 1 then' call fee income 'when a0.cate _ id_2 = 5 then 'local' when a0.cate _ id_3 = 31 then 'domestic long' when a0.cate _ id_3 = 32 then 'International long' when a0.cate _ id_3 = 33 then 'Hong Kong, Macao and Taiwan long' when a0.cate _ id_2 = 7 t Comment 'Internet revenue 'When a0.cate _ id_2 = 8 then' resource rental 'when a0.cate _ id_1 = 3 then' value-added and comprehensive information revenue' When a0.cate _ id_1 = 1000 then' mobile revenue 'When a0.cate _ id_3 = 1110 then' mobile monthly rental revenue 'when a0.cate _ id_3 = 1120 then' mobile phone bill revenue 'when a0.cate _ id_3 = 1200 then' mobile value-added revenue 'when a0.cate _ id_3 = 1400 then' other mobile incomes 'else' other incomes 'end acct_type_name, a1.acct _ item_tp_id detail_acct_type_id, a1.acct _ tp_nm detail_acct_type_name, CAS E when a0.cate _ id_1 in (1, 28) then 2 -- fixed talk income when a0.cate _ id_1 = 28 then 3 -- monthly rent when a0.cate _ id_1 = 1 then 4 -- call income when a0.cate _ id_2 = 5 then 5 -- local when a0.cate _ id_3 = 31 then 6 -- domestic long distance when a0.cate _ id_3 = 32 then 7 -- international long distance when a0.cate _ id_3 = 33 then 8 -- Hong Kong, Macao and Taiwan long distance when a0.cate _ id_2 = 7 then 9 -- Internet revenue when a0.cate _ id_2 = 8 then 10 -- resource income when a0.cate _ id_1 = 3 then 11 -- value-added and comprehensive information income when a0.cate _ id_1 = 100 0 then 12 -- mobile income when a0.cate _ id_3 = 1110 then 13 -- Mobile monthly rental income when a0.cate _ id_3 = 1120 then 14 -- Mobile Phone Fee income when a0.cate _ id_3 = 1200 then 15 -- Mobile value-added income when a0.cate _ id_3 = 1400 then 16 -- other mobile income 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, 'total '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 allselect 2 as acct_type _ ID, 'monthly rent and call name' 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, 'monthly rent '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. AC Ct_item_tp_cate_id = S. acct_item_tp_cate_id and S. cate_id_1 = 28 Union allselect 4 as acct_type_id, 'Call income '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 allselect 5 as acct_type_id, 'local' 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 allselect 6 as acct_type_id, 'domestic loan' 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 = 31uni On allselect 7 as acct_type_id, 'International extension' 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 allselect 8 as acct_type_id, 'Hong Kong, Macao and Taiwan change' 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_ite M_tp_cate_tree s on t. acct_item_tp_cate_id = S. acct_item_tp_cate_id and S. cate_id_3 = 33 Union allselect 9 as acct_type_id, 'Internet revenue '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 allselect 10 as acct_type_id, 'Resource rental '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 allselect 11 as acct_type_id, 'value-added and comprehensive information' 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 allselect 12 as acct_type_id, 'others' 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 (, 1, 3, 2) Union allselect-1 as acct_type_id, 'unknown 'As acct_type_name,-1 as acct_item_tp_id, 'unknown' As acct_tp_nm, 13 S 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 ". "outputs" as select 'A' city_id, 1 ods_city_id, 'nanjing 'city_name, '025' city_code, '000000' zip_code, 3 admin_lo_id, 'ipd _ in_nj' ipd_in_user, 'none' sname, 25 id_prefix, 250 latn_id from sysibm. comment 'B' city_id, 2 ods_city_id, 'wuxi 'city_name, '000000' city_code, '000000' zip_code, 15 admin_lo_id, 'ipd _ in_wx 'ipd_in_user, 'si' sname, 10 id_prefix, 510 latn_id from sysibm. export 'C' city_id, 3 ods_city_id, 'zhenjiang 'city_name, '000000' city_code, '000000' zip_code, 4 admin_lo_id, 'ipd _ in_zj 'ipd_in_user, 'town' sname, 40 id_prefix, 511 latn_id from sysibm. comment 'D' city_id, 4 ods_city_id, 'suzhou 'city_name, '000000' city_code, '000000' zip_code, 20 admin_lo_id, 'ipd _ in_sz 'ipd_in_user, 'su' sname, 12 id_prefix, 512 latn_id from sysibm. comment 'E' city_id, 5 ods_city_id, 'nantong 'city_name, '000000' city_code, '000000' zip_code, 26 admin_lo_id, 'ipd _ in_nt 'ipd_in_user, 'pass 'sname, 13 id_prefix, 513 latn_id from sysibm. comment 'F' city_id, 6 ods_city_id, 'yangzhou 'city_name, '000000' city_code, '000000' zip_code, 33 admin_lo_id, 'ipd _ in_yz 'ipd_in_user, 'yang' sname, 14 id_prefix, 514 latn_id from sysibm. comment 'G' city_id, 7 ods_city_id, 'yancheng 'city_name, '000000' city_code, '000000' zip_code, 39 admin_lo_id, 'ipd _ in_yc 'ipd_in_user, 'sale' sname, 15 id_prefix, 515 latn_id from sysibm. comment 'H' city_id, 8 ods_city_id, 'xuzhou 'city_name, '000000' city_code, '000000' zip_code, 48 admin_lo_id, 'ipd _ in_xz 'ipd_in_user, 'xue' sname, 16 id_prefix, 516 latn_id from sysibm. require 'I 'city_id, 9 ods_city_id, 'huaian 'city_name, '000000' city_code, '000000' zip_code, 60 admin_lo_id, 'ipd _ in_ha' ipd_in_user, 'huai 'sname, 17 id_prefix, 517 latn_id from sysibm. comment 'J' city_id, 10 ods_city_id, 'lianyungang 'city_name, '000000' city_code, '000000' zip_code, 63 admin_lo_id, 'ipd _ in_lyg 'ipd_in_user,' connected 'sname, 18 id_prefix, 518 latn_id from sysibm. comment 'K' city_id, 11 ods_city_id, 'changzhou 'city_name, '000000' city_code, '000000' zip_code, 69 admin_lo_id, 'ipd _ in_cz 'ipd_in_user, 'Chang' sname, 19 id_prefix, 519 latn_id from sysibm. else 'l' city_id, 12 ods_city_id, 'taizhou 'city_name, '000000' city_code, '000000' zip_code, 79 admin_lo_id, 'ipd _ in_tz 'ipd_in_user, 'Ta' sname, 23 id_prefix, 523 latn_id from sysibm. comment 'M' city_id, 13 ods_city_id, 'suqian 'city_name, '000000' city_code, '000000' zip_code, 84 admin_lo_id, 'ipd _ in_sq 'ipd_in_user, 'sled' sname, 27 id_prefix, 527 latn_id from sysibm. sysdummy1unionselect 'Z' city_id, 0 ods_city_id, 'province-wide '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, 'province' 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 ";