CREATE VIEW SD_HOSPITAL.VW_YJ_TXMCJD
 (
 GHID00, BRXM00, GHH000, JZBZ00, DYRQTM, DYSJTM, TXM000, XMMC00, KDKSBH, 
YJKSBH, XB0000, NL0000, BRID00, CYGLX0, PYSM00, XMZT00, DBTXM0, CXGYS0, 
ZXKS00

 )
 AS 
select
  distinct ZYGHID, BRXM00, ZYHGHH, DECODE(JZBZ00,'Y','急诊',''), DYRQTM, DYSJTM, TXM000, SUBSTR(SF_BQ_JYXMMC(TXM000),1,100),
  KDKSBH, YJKSBH, XB0000, NL0000, BRID00,(select CYGLX0 from BM_ZLZD00 where ZLXMID=B.ZLXMID) CYGLX0,' ' PYSM00,B.XMZT00,b.DBTXM0,
  (select cxgys0 from BM_ZLZD00 where ZLXMID=B.ZLXMID) cxgys0,
  (select BMMC00 from BM_BMBM00 where BMBH00=B.YJKSBH)zxks00
From YJ_YW0000 B
  where NVL(TXM000,'0') <> '0' and NVL(CXDJH0, 0) = 0 and CXSL00 = 0 --and NOT EXISTS (SELECT 1 FROM YJ_YW0000 YW WHERE B.YJDJH0=YW.CXDJH0 AND B.ZYGHID=YW.ZYGHID)
    and XMZT00 in('1','2','3')
/