create or replace view vw_zs_ryrjbrmx
(rzks00, ksmc00, zyid00, brid00, ljid01, ljbz00, ljid00, ryrq00, cyrq00, ryzdmc, zdm000, sjcyrq, rycwh0, zyh000, xm0000, zrzcid)
as
--chenzh 2019.08.15  e.ICD000=c.ICD000 修改为e.ICD000=trim(c.ICD000) 
select RZKS00,
       (select BMMC00 from BM_BMBM00 where BMBH00=a.RZKS00) KSMC00,
       ZYID00,
       BRID00,
       nvl((select d.LJID00 from BQ_BRZDXX b,LJ_MBJRTJ c,LJ_MB0000 d,LJ_ZDKSFP e where ZYID00=a.ZYID00 
                 and trim(b.ZDM000)=trim(c.ICD000) and c.TJZT00='0' and c.LJID00=d.LJID00 and d.LJZT00<>'0' 
   and  e.ICD000=trim(c.ICD000) and e.KSBH00=a.RZKS00 and  rownum<=1),0) as LJID01,
       nvl((select distinct 1 from LJ_ZRZC00 where ZYID00=a.ZYID00 and BRID00=a.BRID00 ),0) LJBZ00,
       nvl((select LJID00 from LJ_ZRZC00 where ZRZCID in(select max(ZRZCID) from LJ_ZRZC00 where ZYID00=a.ZYID00)),0),
       a.RYRQ00,
       a.CYRQ00,
       '' RYZDMC,
       nvl((select e.ZDM000 from LJ_ZDKSFP d,BQ_BRZDXX e,LJ_MBJRTJ f where trim(f.ICD000)=trim(e.ZDM000) and e.ZYID00=a.ZYID00 and d.ICD000=f.ICD000 and d.KSBH00=a.RZKS00 and rownum<=1),0)  ZDM000,
       SJCYRQ,
       RYCWH0,    --床位号
       ZYH000,    --住院号
       XM0000,     --姓名       
       nvl((select max(ZRZCID) from LJ_ZRZC00 where ZYID00=a.ZYID00),0)  ZRZCID
     from ZY_BRXXB0 a  where substr(BRZT00,1,1)<>'9'