create or replace view d_ba_brzyxx as
select a.bahm as BINGAH,
a.zycs as ZHUYCSH,
round((SYSDATE - a.csny)/365) AS NIANL,
‘‘ as JIANKKH,
(select name from d_yilifukuan where d_yilifukuan.code=b.ylfffs ) as YILFKFSH,
b.ylfffs as D_YILIAOFUKUANFANGSHI_CODE,
(select name from d_ruyuantujing where d_ruyuantujing.code=d.rytj ) as RUYTJ,
d.rytj as D_RUYUANTUJING_CODE,
‘‘ as RUYTJBZH,
a.ryrq as RUYSHJ,
(select ksmc from d_ksdm where d_ksdm.ksdm=d.ryks) as RUYKB,
d.ryks as D_KEBIE_CODE_RUYKB,
(select ksmc from d_ksdm where d_ksdm.ksdm=d.rybq) as RUYBF,
d.rybq as D_BINGFANG_CODE_RUYBF,
d.cyrq as CHUYSHJ,
(select ksmc from d_ksdm where d_ksdm.ksdm=d.cyks) as CHUYKB,
d.cyks as D_KEBIE_CODE_CHUYKB,
(select ksmc from d_ksdm where d_ksdm.ksdm=d.cybq) as CHUYBF,
d.cybq as D_BINGFANG_CODE_CHUYBF,
d.sjbz,
(select ygxm from gy_ygdm where a.szys=gy_ygdm.ygdm) as KEZHR,
a.szys as D_ZHIGONG_CODE_KEZHR,
‘‘ as ZHURYSH,
‘‘ as D_ZHIGONG_CODE_ZHURYSH,
(select ygxm from gy_ygdm where a.zzys=gy_ygdm.ygdm) as ZHUZHYSH,
a.zzys as D_ZHIGONG_CODE_ZHUZHYSH,
(select ygxm from gy_ygdm where a.zyys=gy_ygdm.ygdm) as ZHUYYSH,
a.zyys as D_ZHIGONG_CODE_ZHUYYSH,
‘‘ as ZERHSH,
‘‘ as D_ZHIGONG_CODE_ZERHSH,
‘‘ as JINXYSH,
‘‘ as D_ZHIGONG_CODE_JINXYSH,
‘‘ as SHIXYSH,
‘‘ as D_ZHIGONG_CODE_SHIXYSH,
‘‘ as ZHUZHENYSH,
‘‘ as D_ZHIGONG_CODE_ZHUZHENYSH,
‘‘ as BIANMY,
‘‘ as D_ZHIGONG_CODE_BIANMY,
‘‘ as BINGAZHL,
‘‘ as D_BINGAHZHILIANG_CODE,
(select name from d_ruyuanbingqing where d_ruyuanbingqing.code=a.ryqk) as RUYQK,
a.ryqk as D_RUYUANQINGKUANG_CODE,
(select ygxm from gy_ygdm where gy_ygdm.ygdm=a.zkry ) as ZHIKYSH,
a.zkry as D_ZHIGONG_CODE_ZHIKYSH,
a.zkrq as ZHIKRQ_YISH,
‘‘ as ZHIKHSH,
‘‘ as D_ZHIGONG_CODE_ZHIKHSH,
‘‘ as ZHIKRQ_HUSH,
(select name from d_liyuanfangshi where d_liyuanfangshi.code= d.lyfs )as LIYFSH,
d.lyfs as D_LIYUANFANGSHI_CODE,
‘‘ as LIYFSHBZH,
d.zryjh as ZAIZHY,
d.zrymd as ZAIZHYMD,
‘‘as HUNMSHJ_RUYQ_TIAN,
‘‘as HUNMSHJ_RUYQ_SHI,
‘‘as HUNMSHJ_RUYQ_FEN,
‘‘as HUNMSHJ_CHUYQ_TIAN,
‘‘as HUNMSHJ_CHUYQ_SHI,
‘‘as HUNMSHJ_CHUYQ_FEN,
‘‘as HUNMSHJ_RUYQ,
‘‘as HUNMSHJ_RUYH,
‘‘as CREATEDATE,
‘‘as UPDATEDATE,
‘‘ as RYTJSHM,
d.zyts as ZYTS,
d.xsecstz as CHUSHTZH,
d.xserytz as RUYTZH,
case when
instr(c.rynl,‘月‘)<>0 and instr(c.rynl,‘岁‘)<>0 then substr(c.rynl,instr(c.rynl,‘岁‘)+1,‘月‘)-instr(c.rynl,‘岁‘)-1)
when
instr(c.rynl,‘月‘)<> 0 and instr(c.rynl,‘岁‘)=0 then substr(c.rynl,1,‘月‘)-1) else null end as NIANL_YUE,
case when instr(c.rynl,‘天‘)<>0 and instr(c.rynl,‘月‘)<>0 then substr(rynl,instr(rynl,‘月‘)+1,‘天‘)-instr(rynl,‘月‘)-1) when instr(c.rynl,‘天‘)<> 0 and instr(c.rynl,‘月‘)=0 then substr(rynl,‘天‘)-1) else null end as NIANL_RI,case when instr(rynl,‘小‘)<>0 then substr(rynl,‘小‘)-1) else null end as NIANL_SHI,‘‘as JIWZBL,‘‘ as YNBL,‘‘ as SHIFSZ,a.zyh as HIS_KEYNO,‘‘ as NianLing_F,‘‘ as EMR_NO,‘‘ as qjcs,‘‘ as cgcs,‘‘ as szqx_zhou,‘‘as szqx_yue,‘‘ as szqx_nianfrom zy_brry a,ba_brda b,ys_zy_jzjl c,ba_brsy dwhere a.zyh=b.zyhand a.zyh=c.jzhmand a.zyh=d.zyhand a.cypb in (1,8);
