SET SCHEMA EDWDBA; SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","EDWDBA"; CREATE VIEW "DM1"."V_DIM_GRP_AREA_DETAIL" AS select par_area_id,par_area_name,area_id,area_name,level, rank()over(order by level desc, case when area_id=par_area_id then 1 else 0 end,par_area_id,area_id) show_order, detail_area_id,detail_area_name from ( select cast(null as integer) par_area_id,cast(null as varchar(20)) par_area_name, admin_lo_id_2 area_id,substr(admin_lo_nm_2,1,9) area_name,0 level, admin_lo_id_4 detail_area_id,admin_lo_nm_4 detail_area_name from bml.admin_lo_d union select distinct cast(null as integer) par_area_id,cast(null as varchar(20) ) par_area_name, admin_lo_id_2 area_id,substr(admin_lo_nm_2,1,9) area_name,0 level, admin_lo_id_3 detail_area_id,admin_lo_nm_3 detail_area_name from bml.admin_lo_d where admin_lo_id_3<>1000 union select distinct cast(null as integer) par_area_id,cast(null as varchar(20) ) par_area_name, admin_lo_id_2 area_id,substr(admin_lo_nm_2,1,9) area_name,0 level, admin_lo_id_2 detail_area_id,admin_lo_nm_2 detail_area_name from bml.admin_lo_d union select admin_lo_id_2 par_area_id,substr(admin_lo_nm_2,1,9) par_area_name, admin_lo_id_3 area_id, case when admin_lo_nm_3<>'连云港' then substr(admin_lo_nm_3,1,6) else admin_lo_nm_3 end area_name,1 level, admin_lo_id_4 detail_area_id,admin_lo_nm_4 detail_area_name from bml.admin_lo_d union select distinct admin_lo_id_2 par_area_id,substr(admin_lo_nm_2,1,9) par_area_name, admin_lo_id_3 area_id, case when admin_lo_nm_3<>'连云港' then substr(admin_lo_nm_3,1,6) else admin_lo_nm_3 end area_name,1 level, admin_lo_id_3 detail_area_id, case when admin_lo_nm_3<>'连云港' then substr(admin_lo_nm_3,1,6) else admin_lo_nm_3 end||'待定' detail_area_name from bml.admin_lo_d where admin_lo_id_3<>1000 union select distinct admin_lo_id_2 par_area_id,substr(admin_lo_nm_2,1,9) par_area_name, admin_lo_id_2 area_id,substr(admin_lo_nm_2,1,9)||'待定' area_name,1 level, admin_lo_id_2 detail_area_id,substr(admin_lo_nm_2,1 ,9)||'待定' detail_area_name from bml.admin_lo_d where admin_lo_id_3<>1000 union select distinct admin_lo_id_2 par_area_id,substr(admin_lo_nm_2,1,9)|| '待定' par_area_name, admin_lo_id_2 area_id,substr(admin_lo_nm_2,1,9) ||'未知' area_name,2 level, admin_lo_id_2 detail_area_id,substr( admin_lo_nm_2,1,9)||'未知' detail_area_name from bml.admin_lo_d union select distinct admin_lo_id_3 par_area_id, case when admin_lo_nm_3<>'连云港' then substr(admin_lo_nm_3,1,6) else admin_lo_nm_3 end par_area_name, admin_lo_id_4 area_id,admin_lo_nm_4 area_name,2 level, admin_lo_id_4 detail_area_id,admin_lo_nm_4 detail_area_name from bml.admin_lo_d union select distinct admin_lo_id_3 par_area_id, case when admin_lo_nm_3<>'连云港' then substr(admin_lo_nm_3,1,6) else admin_lo_nm_3 end par_area_name, admin_lo_id_3 area_id, case when admin_lo_nm_3<>'连云港' then substr(admin_lo_nm_3,1,6) else admin_lo_nm_3 end||'待定' area_name,2 level, admin_lo_id_3 detail_area_id, case when admin_lo_nm_3<>'连云港' then substr(admin_lo_nm_3,1,6) else admin_lo_nm_3 end||'待定' detail_area_name from bml.admin_lo_d where admin_lo_id_3<>1000 ) v; GRANT SELECT ON TABLE "DM1"."V_DIM_GRP_AREA_DETAIL" TO USER "CAODM"; GRANT CONTROL ON TABLE "DM1"."V_DIM_GRP_AREA_DETAIL" TO USER "EDWDBA";