DB2 view 3

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 _ 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 then 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 th En 'Internet revenue 'When a0.cate _ id_2 = 8 then' resource rental 'when a0.cate _ id_1 = 3 then' value-added and comprehensive information revenues '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, Case 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 = 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 Fee income when a0.cate _ id_3 = 1200 then 15 -- mobile value-added revenue when a0.cate _ id_3 = 1400 then 16 -- move other incomes 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 ";