CREATE OR REPLACE VIEW SD_HOSPITAL.VW_ZY_ZYDJB0
 (
 ZYID00, ZYH000, RYRQ00, BQMC00, RYCWH0, CZYXM0, CYRQ00, CYYGXM, BRXM00, 
BRXB00, BRCSRQ, BRLXDZ, BRLXDH, PJXH00, PJJE00, FBMC00, RYZDMC, BRJTDZ, 
BRDH00, CYZYZD

 )
 AS 
select
      A.ZYID00,
      A.ZYH000,
      A.RYRQ00,
      B.BMMC00,
      A.RYCWH0,
      A.CZYXM0,
      A.CYRQ00,
      A.CYYGXM,
      C.BRXM00,
      C.BRXB00,
      C.BRCSRQ,
      C.BRLXDZ,
      nvl(nvl(nvl(C.BRLXDH,c.brdh00),BRGRDH ),GZDWDH),
      (select PJXH00       from  ZY_PJSYQK  where PJH000=(select  max(PJH000) from zy_jzb000 where ZYID00=A.ZYID00)),
      (select sum(BRZJE0)  from  zy_jzb000  where ZYID00=A.ZYID00),
      D.FBMC00,
      (select e.ZDMC00 from BQ_BRZDXX E where a.zyid00=e.zyid00 and e.ZDLB00='2' and rownum=1),--A.RYZDMC,
      C.BRJTDZ,
      C.BRDH00,
      (select e.ZDMC00 from BQ_BRZDXX E where a.zyid00=e.zyid00 and e.ZDLB00='3' and rownum=1)--CYZYZD,
from  zy_brxxb0 A,bm_bmbm00 B,bm_brxxb0 C,bm_brfbb0 D
where A.DQBQ00 = B.BMBH00
  and A.BRID00 = C.BRID00
  and A.FBBH00 = D.FBBH00
/