create or replace view vw_bq13_cyhzhf as
select
   SF_BQ13_CSRQTONL(A.CSRQ00,A.RYRQ00) NL0000,
   a.XB0000,
   a.ZYID00,
   a.XM0000,
   a.ZYH000,
   a.DQBQ00,
   (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQBQ00) DQBQMC,
   a.DQKS00,
   (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQKS00) DQKSMC,
   a.CYRQ00,
   a.CYSJ00,
   a.SJCYRQ,
   substr(CYRQ00, 1, 4)||'-'||substr(CYRQ00,5,2)|| '-' || substr(CYRQ00, 7, 2) || ' ' || substr(CYSJ00, 1, 5) CYRQSJ,
   to_date(a.SJCYRQ,'yyyymmdd')-to_date(a.SJRYRQ,'yyyymmdd')||'天' as SJZYTS,
   (select nvl(BRDH00, BRLXDH) from BM_BRXXB0 where BRID00 = a.BRID00) BRDH00,--病人电话
   nvl(nvl((select ZDMC00 from BQ_BRZDXX where ZDLB00 = '3' and ZYID00 = a.ZYID00 and rownum = 1),(select ZDMC00 from BQ_BRZDXX where ZDLB00 = '2' and ZYID00 = a.ZYID00 and rownum = 1)),nvl((select ZDMC00 from BQ_BRZDXX where ZDLB00 = '3' and ZYID00 = a.ZYID00 and rownum = 1),(select ZDMC00 from BQ_BRZDXX where ZDLB00 = 'c' and ZYID00 = a.ZYID00 and rownum = 1))) ZDMC00, --诊断名称
   b.HFRQ00, --回访日期
   b.YYXJ00, --用药宣教
   b.YSXJ00, --饮食宣教
   b.KFXJ00, --康复宣教
   b.GLXJ00, --管理宣教
   b.HFR000, --回访人工号
   b.HFRXM0, --回访人姓名
   b.BZ0000, --备注
   b.PJ0000, --患者评价
   b.LRRQ00, --录入日期
   b.LRSJ00, --录入时间
   b.HFNR00,  --自定义回访内容
   a.RYCWH0,--入院床位号
   a.SXYSXM --主治医生
from ZY_BRXXB0 a , BQ_CYHZHF b
where a.ZYID00=b.ZYID00(+) and a.BRZT00 in ('3','4','5') and a.SJCYRQ<>'20991231'
order by a.sjcyrq;