CREATE OR REPLACE VIEW SD_HOSPITAL.VW_TJ_DWJHD0
 (
 DWZBID, ZBMC00, ZLXMID, BH0000, MC0000, XSSX00, ZLXMJC, SFJE00, ZHJE00, 
DWMC00, DWID00, JCYY00

 )
 AS 
select
  A.DWZBID,C.ZBMC00,A.ZLXMID,B.BH0000,B.MC0000,A.XSSX00,
  (SELECT ZLXMJC FROM BM_ZLZD00 WHERE ZLXMID=A.ZLXMID) ZLXMJC,
  (select SFJE00 from VW_TJ_ZHXM00 where ZLXMID=A.ZLXMID and ROWNUM=1) SFJE00,
  (select SFJE00*A.XMZKL0/100 from VW_TJ_ZHXM00 where ZLXMID=A.ZLXMID and ROWNUM=1) ZHJE00,
  (select DWMC00 from bm_tydwb0 where dwid00=c.dwid00) DWMC00, DWID00,
  (select JCYY00 from VW_TJ_ZHXM00 where ZLXMID=A.ZLXMID and ROWNUM=1) JCYY00
from TJ_DWZBMX A, TJ_ZHXM00 B, TJ_DWZB00 C  
where A.TCBH00<>0 AND A.DWZBID = C.DWZBID and A.ZLXMID=B.ZLXMID AND B.SFYX00='Y'
  AND EXISTS(SELECT 1 FROM BM_ZLZD00 WHERE ZLXMID=A.ZLXMID AND SFYXSQ='Y') 
order by A.DWZBID,b.bh0000,B.XH0000,A.XSSX00
/