sql

create or replace view application_basic as
select col1,col2,col3,col4,col5,col6,col7,col8,col9,num as col10,trunc((num1+num2+num3),2) as col11,trunc(((num1+num2+num3)/num),2) as col12,col13,col14,col15,col16,col17,col18,col19 from
(select a.apply_no as col1,f.id as col2,f.name as col3,f.relation_to_apply_man as col4,f.iden_id as col5,f.health as col6,f.accountaddr as col7,f.nowaddr as col8,f.unit_title as col9
  ,(select count(apply_no) from  apply_family_member s where s.apply_no=f.apply_no  group by s.apply_no ) as num
  ,(select  decode(avg(s1.actual_income),'',0,avg(s1.actual_income)) from apply_family_member u ,income_city_salary s1
where u.id=s1.family_members_id(+) and u.id=f.id group by u.id) as num1,
(select  decode(avg(s2.pensions),'',0,avg(s2.pensions)) from apply_family_member u ,income_social_insurance s2
where u.id=s2.family_members_id(+) and u.id=f.id group by u.id) as num2,
(select  decode(avg(s3.other_income),'',0,avg(s3.other_income)) from apply_family_member u ,income_other s3
where u.id=s3.family_members_id(+) and u.id=f.id group by u.id) as num3,
c.comm_name as col13,
c.comm_no as col14,
c.area_no as col15,
c.city_no as col16,
c.street_no as col17,
c.sheng_no as col19,
to_char(a.apply_date,'yyyy') as col18
   from  application a,apply_family_member f,Community_Inf c  
   where a.apply_no=f.apply_no and c.comm_no=a.comm_no  order by a.comm_no asc
    )

 

 

 

 

 

 

 

create or replace view application_detail as
select col1,col2,col3,col4,col5,col6,col7,col8,col9,num as col10,trunc((num1+num2+num3),2) as col11,trunc(((num1+num2+num3)/num),2) as col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29
,col30,col31,col32,col33,col34,col35,col36,col37,col38,col39,col40,col41,col42,col43,col44,col45,col46,col47,col48,col49,col50
,col51,col52,col53,col54,col55,col56,col57,col58,col59,col60,col61,col62,col63,col64,col65  from
(
select a.apply_no as col1,f.id as col2,f.name as col3,f.relation_to_apply_man as col4,f.iden_id as col5,f.health as col6,f.accountaddr as col7,f.nowaddr as col8,f.unit_title as col9,

(select count(apply_no) from  apply_family_member s where s.apply_no=f.apply_no  group by s.apply_no ) as num,
(select  decode(avg(s1.actual_income),'',0,avg(s1.actual_income)) from apply_family_member u ,income_city_salary s1 where u.id=s1.family_members_id(+) and u.id=f.id group by u.id) as num1,
(select  decode(avg(s2.pensions),'',0,avg(s2.pensions)) from apply_family_member u ,income_social_insurance s2 where u.id=s2.family_members_id(+) and u.id=f.id group by u.id) as num2,
(select  decode(avg(s3.other_income),'',0,avg(s3.other_income)) from apply_family_member u ,income_other s3 where u.id=s3.family_members_id(+) and u.id=f.id group by u.id) as num3,
   decode(f.identity,'下岗','1','') as col13,
decode(f.identity,'无业','1','') as col14,
decode(f.identity,'打零工','1','') as col15,
decode(h.equity_Type,'私有','1','') as col16,
decode(h.equity_Type,'租住','1','') as col17,
decode(h.equity_Type,'借住','1','') as col18,
h.use_Area as col19,
decode(f.iiiName,'癌症','1','') as col20,
decode(f.iiiName,'尿毒症','1','') as col21,
decode(f.iiiName,'血液透析','1','') as col22,
decode(f.iiiName,'白血病','1','') as col23,
decode(f.iiiName,'慢性病','1','') as col24,
decode(f.iiiName,'其他特殊疾病','1','') as col25,
decode(f.disability,'二级（重度）肢体残疾','1',(decode(f.disability,'一级（重度）肢体残疾','1',''))) as col26,

decode(f.disability,'二级（重度）肢体残疾','1',(decode(f.disability,'一级（重度）肢体残疾','1',''))) as col27,
decode(f.disability,'一级智力残疾','1',(decode(f.disability,'二级智力残疾','1',''))) as col28,
decode(f.disability,'视力残疾一级盲','1',(decode(f.disability,'视力残疾二级盲','1',''))) as col29,
decode(f.disability,'聋哑重残一级','1',(decode(f.disability,'聋哑重残二级','1',''))) as col30,

decode(f.disability,'其它重残（二级以上）','1','') as col31,

decode(f.disability,'三级（重度）肢体残疾','1','') as col32,
decode(f.disability,'三级（重度）精神残疾','1','') as col33,
decode(f.disability,'三级智力残疾','1',(decode(f.disability,'四级智力残疾','1',''))) as col34,
decode(f.disability,'视力残疾三级盲','1',(decode(f.disability,'视力残疾四级盲','1',''))) as col35,
decode(f.disability,'聋哑重残三级','1',(decode(f.disability,'聋哑重残四级','1',''))) as col36,
decode(f.disability,'其他重残（三级以下）','1','') as col37,
--学历
decode(f.education,'小学','1','') as col38,
decode(f.education,'中学','1','') as col39,
decode(f.education,'高中','1','') as col40,
decode(f.education,'本科','1','') as col41,

(select (case when (trunc(months_between(sysdate,g.born_date)/12)+1) between 60 and 69 then '1' end) AS AGE from apply_family_member g where  f.id=g.id ) as col42,
(select (case when (trunc(months_between(sysdate,g.born_date)/12)+1) between 70 and 79 then '1' end) AS AGE from apply_family_member g where  f.id=g.id ) as col43,
(select (case when (trunc(months_between(sysdate,g.born_date)/12)+1) between 80 and 89 then '1' end) AS AGE from apply_family_member g where  f.id=g.id ) as col44,
(select (case when (trunc(months_between(sysdate,g.born_date)/12)+1)>=90 then '1' end) AS AGE from apply_family_member g where  f.id=g.id ) as col45,
decode(f.FAMILY_TYPE,'伤残军人','1','') as col46,
decode(f.FAMILY_TYPE,'在乡军人','1','') as col47,
decode(f.FAMILY_TYPE,'复员军人','1','') as col48,
decode(f.FAMILY_TYPE,'烈属','1','') as col49,
decode(f.FAMILY_TYPE,'华侨','1','') as col50,
decode(f.FAMILY_TYPE,'朝侨','1','') as col51,
decode(f.FAMILY_TYPE,'三无对象','1','') as col52,

decode(f.INSURANCE,'医疗保险','1','') as col53,
decode(f.INSURANCE,'养老保险','1','') as col54,
a.phone as col55,
c.comm_name as col56,
c.comm_no as col57,
c.street_no as col58,
c.city_no as col59,
c.area_no as col60,

decode(a.gr,'集中供热','1','') as col61,
decode(a.gr,'小区供热','1','') as col62,
decode(a.gr,'其它','1','') as col63,
to_char(a.apply_date,'yyyy') as col64,
c.sheng_no as col65
 

   from  application a,apply_family_member f,income_house h,Community_Inf c  
   where a.apply_no=f.apply_no and c.comm_no=a.comm_no and f.id=h.family_members_id(+) order by a.apply_no asc
    ) apply