create or replace view vw_bq_brctk
(zyid00, brid00, dqbq00, dqbqmc, dqks00, dqksmc, zyh000, rycwh0, xm0000, xb0000, brnl00, ryrq00, rysj00, zdmc00, zghs00, zghsxm, sxys00, sxysxm, hljb00, brgmjl, ssmc00, brzt00, mrn000, qrurl0, ysqk00, hszxm0)
as
select ZYID00,
        BRID00,
       DQBQ00,
       DQBQMC,
       DQKS00,
       DQKSMC,
       ZYH000,
       RYCWH0,
       XM0000,
       XB0000,
        BRNL00,
       RYRQ00,
       RYSJ00,
        ZDMC00,
       ZGHS00,
        ZGHSXM,
       SXYS00,
        SXYSXM,
       HLJB00,
       decode (BRGMJL,null,'未发现',BRGMJL),
       SSMC00,
       BRZT00,
      (select BRBSM0 from BQ_BRBSM0 where SFYX00='1' and ZYID00=a.ZYID00 and rownum=1)MRN000,
      (select QRURL0 from BQ_BRBSM0 where SFYX00='1' and ZYID00=a.ZYID00 and rownum=1)QRURL0,
      (select to_char(wm_concat(c.ZLXMJC))
         from (select ZYID00,TQRQ00,CLBZ00,ZLXMID,ZLXMJC,decode(YZZT00,'2',decode(sign(to_number(TQRQ00)-to_number(to_char(sysdate,'YYYYMMDD'))),0,'9',1,'9','2'),YZZT00) YZZT00 from  BQ_YJYZ00) c
        inner join BM_ZLZD00 b
                on c.ZLXMID = b.ZLXMID
             where c.ZYID00 = a.ZYID00 and (b.LBBH00 = '8' or (b.LBBH00='9' and ( instr(c.ZLXMJC,'饮食')>0 or instr(c.zlxmjc,'普食')>0 or instr(c.zlxmjc,'禁食')>0 or instr(c.zlxmjc,'流质')>0)))
             and (c.YZZT00 in ('1', '2', '9') or
              (c.TQRQ00 >= to_char(sysdate, 'YYYYMMDD') and c.YZZT00 = 3 and c.CLBZ00 = 1))) YSQK00,
       HSZXM0 --护士长姓名

from VW_BQ_ZYBRLB_YDHL a
;