CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BQ_BRZDXX
 (
 ID0000, BRID00, ZYID00, ZDLB00, ZDM000, ZDMC00, ZDRQ00, ZDSJ00, SFYZ00, 
ZLXG00, YGBH00, SFYZMC, ZLXGMC, ZDLBMC, CRDJID, SFCRB0, YQBGBZ, ICDJMC, 
BZ0000, FZBM00, FZMC00, RYBQ00, PLXH00, MAXXH0

 )
 AS 
select
    T1.ID0000, T1.BRID00, T1.ZYID00, T1.ZDLB00, T1.ZDM000, TRIM(T1.ZDMC00) ZDMC00,
    TO_CHAR(TO_DATE(T1.ZDRQ00, 'YYYY.MM.DD'), 'YYYY.MM.DD') ZDRQ00,
    TO_CHAR(TO_DATE(T1.ZDSJ00, 'HH24:MI:SS'), 'HH24:MI:SS') ZDSJ00,
    T1.SFYZ00, T1.ZLXG00, T1.LRR000 YGBH00, TRIM(T2.MC0000) SFYZMC,
    TRIM(t3.MC0000) ZLXGMC, TRIM(t4.MC0000) ZDLBMC, T1.CRDJID,
    (SELECT NVL(MAX(SFCRB0), '0') SFCRB0 FROM XT_ICD900 WHERE trim(ICD900(+)) = trim(T1.ZDM000)) SFCRB0,
    T1.YQBGBZ,
    (SELECT TRIM(ICD900)||' '||JBMC00 FROM XT_ICD900 WHERE trim(ICD900)= trim(T1.ZDM000)) ICDJMC,
    T1.BZ0000,
    T1.FZBM00,
    T1.FZMC00,
    T1.RYBQ00,
    nvl(T1.PLXH00,0) PLXH00,
    (select nvl(max(PLXH00),0) from BQ_BRZDXX where ZYID00=T1.ZYID00) MAXXH0
from BQ_BRZDXX T1, XT_SFYZ00 T2, XT_ZLXG00 T3, XT_ZDLB00 T4
  where  T1.SFYZ00 = T2.DM0000 and T1.ZLXG00 = T3.DM0000(+) and T1.ZDLB00 = T4.DM0000
  order by t1.zdsj00
/