create or replace view VW_XM_ZKYYKZ(YPNM00, SFQY00, YPBM00, YPMLBZ, YPTYM0, YPMC00, YPGG00, SCCJZW, LBBH00, 
YLBH00, JXBH00, LBMC00, YLMC00, JXMC00, QYRQSJ, MZZYBZ, MZZYSY, SFXM00)
as
select
  A.YPNM00,
  A.SFQY00,
  B.YPBM00,
  B.YPMLBZ,
  B.YPTYM0,
  B.YPMC00,
  B.YPGG00,
  (select SCCJZW from  BM_YPSCCJ where SCCJBH=B.SCCJBH) SCCJZW,
  B.LBBH00,
  B.YLBH00,
  B.JXBH00,
  (select LBMC00 from BM_YPLB00 where LBBH00=B.LBBH00 ) LBMC00,
  (select YLMC00 from BM_YPYL00 where LBBH00=B.LBBH00 and YLBH00=B.YLBH00 ) YLMC00,
  (select JXMC00 from BM_YPJX00 where LBBH00=B.LBBH00 and JXBH00=B.JXBH00 ) JXMC00,
  A.QYRQ00||' '||A.QYSJ00  QYRQSJ,
  A.MZZYBZ, 
  decode(A.MZZYBZ,'0','门诊','1','住院','2','所有') MZZYSY,
  A.SFXM00
from BM_ZKYYKZ A,BM_YD0000 B where A.YPNM00=B.YPNM00 and A.SFXM00='0'