view_ora.sql.bak

公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统

         MAX(DECODE(SM0000,'place-2',RS,'')) place2, 
         MAX(DECODE(SM0000,'place-3',RS,'')) place3, 
         MAX(DECODE(SM0000,'place-4',RS,'')) place4, 
         MAX(DECODE(SM0000,'place-5',RS,'')) place5, 
         MAX(DECODE(SM0000,'place-6',RS,'')) place6 
    FROM VW_ZZRKTJB1  
GROUP BY zcsy00 
/
--------------------------------------------------------
create or replace view vw_zzrktjb3 
       (code, mesg) as
       select '01','务        工' from dual
union  select '02','务        农' from dual
union  select '03','经        商' from dual
union  select '04','服        务' from dual
union  select '05','因公出差'     from dual
union  select '06','借读培训'     from dual
union  select '07','治病疗养'     from dual
union  select '08','保        姆' from dual
union  select '09','投亲靠友'     from dual
union  select '10','探亲访友'     from dual
union  select '11','旅游观光'     from dual
union  select '12','其        它' from dual   
/
--------------------------------------------------------
CREATE OR REPLACE VIEW VW_ZZRKTJB AS   
  SELECT  V3.MESG     MESG,
          V3.CODE     CODE,
          V2.sex1     sex1,   
          V2.sex2     sex2,  
          V2.time1    time1, 
          V2.time2    time2, 
          V2.time3    time3, 
          V2.from1    from1, 
          V2.from2    from2, 
          V2.from3    from3, 
          V2.from4    from4, 
          V2.from5    from5, 
          V2.from6    from6, 
          V2.place1   place1, 
          V2.place2   place2, 
          V2.place3   place3, 
          V2.place4   place4, 
          V2.place5   place5, 
          V2.place6   place6 
    FROM VW_ZZRKTJB2 V2,VW_ZZRKTJB3 V3
    WHERE V3.CODE = V2.zcsy00(+)
/
--------------------暂住人口统计表结束--------------------------------

-------------------统计出新办暂住证的清单-----------------------------
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
--办证统计-------DATA04为统计时间200010表示为200010%

CREATE OR REPLACE VIEW vw_bztj00
    ( xh0000,xm0000,yfzgx0,fzxm00,
      czhkss,czhkxz,lbdrq0,zcdssx,
      zcdxz0,zcdpcs,zcdjwh,zcdljx,
      zcdmph,yxqx00,yqrq00,tbr000 ) AS   
  SELECT z.xh0000,z.xm0000,z.yfzgx0,z.fzxm00,
         z.czhkss,z.czhkxz,z.lbdrq0,z.zcdssx,
         z.zcdxz0,z.zcdpcs,z.zcdjwh,z.zcdljx,
         z.zcdmph,z.yxqx00,z.yqrq00,z.tbr000  
    FROM zzxxjl Z,zktemp T 
   WHERE rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and 
         rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and 
         rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) = 
         rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) AND z.qfrq00 like rtrim(t.data04)
/


----------------------------延期统计--------------------------------------
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
--办证统计-------DATA04为统计时间200010表示为200010%

CREATE OR REPLACE VIEW vw_yqtj00
    ( xh0000,xm0000,yfzgx0,fzxm00,
      czhkss,czhkxz,lbdrq0,zcdssx,
      zcdxz0,zcdpcs,zcdjwh,zcdljx,
      zcdmph,yxqx00,yqrq00,tbr000 ) AS   
  SELECT z.xh0000,z.xm0000,z.yfzgx0,z.fzxm00,
         z.czhkss,z.czhkxz,z.lbdrq0,z.zcdssx,
         z.zcdxz0,z.zcdpcs,z.zcdjwh,z.zcdljx,
         z.zcdmph,z.yxqx00,z.yqrq00,z.tbr000 
    FROM zzxxjl Z,zktemp T 
   WHERE rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and 
         rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and 
         rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) = 
         rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) AND z.yqrq00 like rtrim(t.data04)
/         


---------------------------暂住人口租赁房屋治安报表--------------

--------------------------租赁房屋-------------------------------

CREATE OR REPLACE VIEW VW_ZZRKZATJ_HOUSE
    ( DQ0000, --地区别
      hs0000, --户数
      js0000, --间数  
      fzs000  --发证数
    ) AS   
  SELECT decode(t.data06,'3',C.CZFSZQ,'2',C.CZFPCS,'1',C.CZFJWH),
         COUNT(distinct(C.fdzj00)),
         count(C.xh0000),   
         COUNT(distinct(C.xkzbh0))  
    FROM CZFXXB C,ZKTEMP T
   WHERE rtrim(decode(t.data06,'1',c.czfszq,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and 
         rtrim(decode(t.data06,'1',c.czfszx,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and 
         rtrim(decode(t.data06,'1',c.czfpcs,'2',substr(c.czfpcs,1,7),'1')) = 
         rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1'))
GROUP BY decode(t.data06,'3',C.CZFSZQ,'2',C.CZFPCS,'1',C.CZFJWH)
/

--------------------------租住人员-------------------------------

CREATE OR REPLACE VIEW VW_ZZRKZATJ_MAN
    ( DQ0000, --地区别
      CK0000, --常口
      ZK0000, --暂口  
      TW0000, --台湾人
      GY0000  --外国人
    ) AS   
  SELECT decode(t.data06,'3',z.zcdssx,'2',z.zcdpcs,'1',z.zcdjwh),
         sum(decode(substr(z.czhkss,1,4),substr(t.data01,1,4),1,0)),
         sum(decode(substr(z.czhkss,1,1),'7',0,'9',0,1)) - sum(decode(substr(z.czhkss,1,4),substr(t.data01,1,4),1,0)),  
         sum(decode(substr(z.czhkss,1,2),'71',1,0)) ,
         sum(decode(substr(z.czhkss,1,2),'72',1,0)) + sum(decode(substr(z.czhkss,1,1),'9',1,0))
    FROM zzxxjl z,ZKTEMP T
   WHERE rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and 
         rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and 
         rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) = 
         rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) AND Z.ZLBZ00 = 'I'
GROUP BY decode(t.data06,'3',z.zcdssx,'2',z.zcdpcs,'1',z.zcdjwh)
/
-----------------------房屋性质-------------------------------
-------计算公房的户数和间数------

CREATE OR REPLACE VIEW VW_ZZRKZATJ_ZFXZ1
    ( DQ0000, --地区别
      HS0000, --户数
      JS0000, --间数
      xz0000  --性质
     ) AS   
  SELECT decode(t.data06,'3',C.CZFSZQ,'2',C.CZFPCS,'1',C.CZFJWH),
         count(distinct(c.fdzj00)),
         count(c.xh0000),
         'gf'
         FROM czfxxb c,ZKTEMP T
   WHERE rtrim(decode(t.data06,'1',c.czfszq,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and 
         rtrim(decode(t.data06,'1',c.czfszx,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and 
         rtrim(decode(t.data06,'1',c.czfpcs,'2',substr(c.czfpcs,1,7),'1')) = 
         rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and ZFXZ00 = '1'
GROUP BY decode(t.data06,'3',C.CZFSZQ,'2',C.CZFPCS,'1',C.CZFJWH)
/
---------计算私房数量----------
CREATE OR REPLACE VIEW VW_ZZRKZATJ_ZFXZ2
    ( DQ0000, --地区别
      HS0000, --户数
      JS0000, --间数
      xz0000  --性质
     ) AS   
  SELECT decode(t.data06,'3',C.CZFSZQ,'2',C.CZFPCS,'1',C.CZFJWH),
         count(distinct(c.fdzj00)),
         count(c.xh0000),
         'Sf'
         FROM czfxxb c,ZKTEMP T
   WHERE rtrim(decode(t.data06,'1',c.czfszq,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and 
         rtrim(decode(t.data06,'1',c.czfszx,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and 
         rtrim(decode(t.data06,'1',c.czfpcs,'2',substr(c.czfpcs,1,7),'1')) = 
         rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and ZFXZ00 = '2'
GROUP BY decode(t.data06,'3',C.CZFSZQ,'2',C.CZFPCS,'1',C.CZFJWH)
/
---------两视图合并-------------
CREATE OR REPLACE VIEW VW_ZZRKZATJ_ZFXZ
    ( DQ0000, --地区别
      HS0000, --户数
      JS0000, --间数
      xz0000  --性质
     ) AS   
  SELECT * from VW_ZZRKZATJ_ZFXZ1 
  union all
  select * from VW_ZZRKZATJ_ZFXZ2
/  
  

-----------------------租赁时间-------------------------------
CREATE OR REPLACE VIEW VW_ZZRKZATJ_TIME
    ( DQ0000, --地区别
      SJ1000, --一个月到半年
      SJ2000, --半年至一年
      SJ3000  --一年以上
     ) AS   
  SELECT decode(t.data06,'3',Z.ZCDSSX,'2',Z.ZCDPCS,'1',Z.ZCDJWH),
         SUM(decode(fun_zzsj(z.lbdrq0),'1',1,0)),
         SUM(decode(fun_zzsj(z.lbdrq0),'2',1,0)),
         SUM(decode(fun_zzsj(z.lbdrq0),'3',1,0))
         FROM zzxxjl z,ZKTEMP T
   WHERE rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and 
         rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and 
         rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) = 
         rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and z.zlbz00 = 'I'
GROUP BY decode(t.data06,'3',Z.ZCDSSX,'2',Z.ZCDPCS,'1',Z.ZCDJWH)
/
-----------------------租房用途-------------------------------

CREATE OR REPLACE VIEW VW_ZZRKZATJ_ZFYT
    ( DQ0000, --地区别
      jz0000, --居住
      ck0000, --仓库  
      sc0000, --生产
      sd0000, --商店
      qt0000  --其他
    ) AS   
  SELECT decode(t.data06,'3',C.CZFSZQ,'2',C.CZFPCS,'1',C.CZFJWH),
         SUM(decode(c.czfyt0,'01',1,0)),
         SUM(decode(c.czfyt0,'02',1,0)),
         SUM(decode(c.czfyt0,'03',1,0)),
         SUM(decode(c.czfyt0,'04',1,0)),
         SUM(decode(c.czfyt0,'05',1,0))
         FROM CZFXXB C,ZKTEMP T
   WHERE rtrim(decode(t.data06,'1',c.czfszq,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and 
         rtrim(decode(t.data06,'1',c.czfszx,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and 
         rtrim(decode(t.data06,'1',c.czfpcs,'2',substr(c.czfpcs,1,7),'1')) = 
         rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1'))
GROUP BY decode(t.data06,'3',C.CZFSZQ,'2',C.CZFPCS,'1',C.CZFJWH)
/   

-------------所有视图合并-----------------------------------------
---本视图把所有子视图联接起来，但是要派出所用户上传暂住
---住人口信息计算出（VW_ZZRKZATJ_MAN  V2），
---否则该所的所有其他信息将无法显示
---字段内容设为S+次序是按治安报表的从左右的顺序


CREATE OR REPLACE VIEW VW_ZZRKZATJ AS   
  SELECT  v2.dq0000 s0,
          v1.hs0000 s1,
          v1.js0000 s2,
          v1.fzs000 s3, 
          v2.CK0000 s4,
          v2.ZK0000 s5,
          v2.TW0000 s6,
          v2.GY0000 s7,
          v3.HS0000 s8,
          v3.JS0000 s9,
          v4.HS0000 s10,
          v4.JS0000 s11,
          v5.SJ1000 s12,
          v5.SJ2000 s13,
          v5.SJ3000 s14,
          v6.jz0000 s15,
          v6.ck0000 s16,
          v6.sc0000 s17,
          v6.sd0000 s18,
          v6.qt0000 s19  
  FROM    VW_ZZRKZATJ_HOUSE  V1, 
          VW_ZZRKZATJ_MAN    V2,
          VW_ZZRKZATJ_ZFXZ1  V3,
          VW_ZZRKZATJ_ZFXZ1  V4,
          VW_ZZRKZATJ_TIME   V5,
          VW_ZZRKZATJ_ZFYT   V6
 WHERE    V2.DQ0000 = V1.DQ0000(+) AND
          V2.DQ0000 = V3.DQ0000(+) AND
          V2.DQ0000 = V4.DQ0000(+) AND
          V2.DQ0000 = V5.DQ0000(+) AND
          V2.DQ0000 = V6.DQ0000(+)
/          
----------------------------治安报表结束-------------------------
-------------查询视图--------------------
drop  VIEW vw_yqxx
/
CREATE VIEW vw_yqxx
( xh0000,zzxxzj,jsrq00,yxq000,sfdj00,sfyy00,
  zcly00,zcrq00,back01,back02,scry00,scrq00,
  zlbz00,bz0000,xm0000,sfzhm0,zzzbh0,zcdpcs)
AS select
  yqb000.xh0000,yqb000.zzxxzj,yqb000.jsrq00,
  yqb000.yxq000,yqb000.sfdj00,yqb000.sfyy00,
  yqb000.zcly00,yqb000.zcrq00,yqb000.back01,
  yqb000.back02,yqb000.scry00,yqb000.scrq00,
  yqb000.zlbz00,yqb000.bz0000,zzxxjl.xm0000,
  zzxxjl.sfzhm0,zzxxjl.zzzbh0,zzxxjl.zcdpcs
  from zk.yqb000,
  zk.zzxxjl where(zzxxjl.xh0000=yqb000.zzxxzj)
/  

CREATE OR REPLACE VIEW vw_zxxx(xh0000,zxyy00,zxrq00,qxss00,
                    qzxz00,zcly00,zcrq00,zlbz00,xm0000,zzzbh0)
      AS   SELECT zzxxzx.xh0000,zzxxzx.zxyy00,zzxxzx.zxrq00,
        zzxxzx.qxss00,zzxxzx.qzxz00,zzxxzx.zcly00,
        zzxxzx.zcrq00,zzxxzx.zlbz00,zzxxjl.xm0000,
        zzxxjl.zzzbh0 FROM zzxxjl,zzxxzx WHERE (zzxxjl.xh0000=zzxxzx.xh0000) 
;