db2视图4 例子

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