CREATE OR REPLACE VIEW VW_BA_ZLBGSJ AS
select BA.BRXM00 Name, --姓名
decode(BA.BRXB00,'男性','1','女性','2','男','1','女','2','9') Sex,--性别
substr(BA.BRCSRQ,1,4)||'-'||substr(BA.BRCSRQ,5,2)||'-'||substr(BA.BRCSRQ,7,2) Birthda,--出生日期
BA.SFZH00 Indno,--身份证号
'' OtherName,-- 其他证件类型
'' OtherNo,--证件号
nvl(BA.JTDH00,BA.LXRDH0) Telephone ,
BA.LXRXM0 ContactPerson, --联系人
BA.LXRGX0 Relation,--关系
BA.LXRDH0 ContactPhone,
BA.ZY0000 Occu,--职业
nvl(MZ.DM0000,'99') Trib,--民族
decode(BA.HYZK00,'未婚','1','已婚','2','丧偶','3','离婚','4','不详',9,9) Mari,
BA.GZDW00 WorkCom,
BA.JTDZ00 Address,
BA.XZZXC0 Conservation,--常住地址
'' Topo,--发病部位
---substr(BA.BLZDBM,2,instr(BA.BLZDBM,'/')-3)  Morp,--病理学编号
nvl(substr(BA.BLZDBM,2,instr(BA.BLZDBM,'/')-3),(select substr(blxbh0,2,instr(blxbh0,'/')-3) from ZS_ZLBGK0 zs where zs.zyid00=ba.lsh000 and SFTJ00<>'2'))  Morp,--病理学编号
--病理类型,只需要4位
(case when  instr(substr(ZD.ZDM000,1,instr(ZD.ZDM000, '.') +1),'.x')>0 then substr(ZD.ZDM000,0, instr(ZDM000, '.') -1)
  when instr(ZD.ZDM000,'.')>0 then substr(ZD.ZDM000,0, instr(ZD.ZDM000, '.') +1)
  else ZD.ZDM000 end) ICD10,
'' Beha,--行为
'' Grad,--分级
'' Stage,--分期
'' T,
'' N,
'' M,
''  Basi,--诊断依据
((SELECT JBMC00 FROM XT_ICD900 XT  WHERE ICD900=ZD.ZDM000 and XTBZ00='1') ||' '|| BLZD00) DiagnosisResult,--诊断结果
BA.BLZD00 DetailedDiagnosisResult,--详细诊断结果
(substr(ZD.ZDRQ00,1,4)||'-'||substr(ZD.ZDRQ00,5,2)||'-'||substr(ZD.ZDRQ00,7,2)) Inciden,--诊断日期
'' TreaInfo,--治疗信息
'' Hosp,--门诊号
BA.ZYH000 Caseno,--住院号
'' Dlc,--最后接触日期
'' State,--最后接触状态
'' Deathda,--死亡日期
'' Caus,--死因编号
'' DeadPlace,--死亡地点
'' DeRepDoct,--死亡报告医师
(substr(BA.CYRQ00,1,4)||'-'||substr(BA.CYRQ00,5,2)||'-'||substr(BA.CYRQ00,7,2)) Reportd,--报告日期
BA.ZYYSMC RepDoct, --报告医师
'' RemarkExt,
BA.CYRQ00 CYRQ00,
BA.JTDZSF,
BA.LSH000,
BA.cykb00
from  BA_BRZYXX BA join BQ_BASZDB ZD on ZD.ZYID00=BA.LSH000
left join XT_MZ0000 MZ on BA.MZ0000=MZ.MC0000
where ZD.ZDLB00 in ('3','4') and ZD.ZDM000 between 'C00' and 'D48'
;